GROUP on one column and aggregate counts based on distinct values in another column

I have a table with values like so:

CREATE TABLE myTable
    (`ts` varchar(2), `usr_id` varchar(1), `data` varchar(1))
;
    
INSERT INTO myTable
    (`ts`, `usr_id`, `data`)
VALUES
    ('11', 'A', 'x'),
    ('11', 'A', 'x'),
    ('11', 'B', 'x'),
    ('11', 'C', 'x'),
    ('11', 'C', 'x'),
    ('11', 'C', 'x'),
    ('22', 'B', 'x'),
    ('33', 'C', 'x'),
    ('33', 'C', 'x'),
    ('33', 'D', 'x'),
    ('33', 'A', 'x')  
;

I want to do a GROUP BY based on ts and display the counts for distinct values of usr_id like below.

ts	count of A	count of B	count of others
11	2	1	3
22	0	1	0
33	1	0	3

http://www.sqlfiddle.com/#!9/bbf4fc/2

I couldn’t make much progress beyond doing the GROUP BY for ts: select ts, usr_id, count(*) from myTable group by ts, usr_id; But I want the counts of rows with A, B and others in the same row, can’t figure out how to achieve that.

Answer

This is your query

SELECT 
    DISTINCT ts, 
    COUNT(CASE WHEN usr_id = 'A' THEN 1 END) AS count_a,
    COUNT(CASE WHEN usr_id = 'B' THEN 1 END) AS count_b,
    COUNT(CASE WHEN usr_id != 'A' AND usr_id != 'B' THEN 1 END) AS count_others
FROM myTable
GROUP BY ts
ORDER BY ts

Demo