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

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.

CodePudding user response：

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

CodePudding user response：

PostgreSQL allows filtering aggregates:

select ts, 
       count(1) filter (where usr_id = 'A') as "count of A",
       count(1) filter (where usr_id = 'B') as "count of B",
       count(1) filter (where usr_id not in ('A', 'B')) as "count of others"
  from mytable
 group by ts;

db<>fiddle here

CodePudding user response：

your sample link is ybout mysql, but

SELECT
  ts
  , SUM(`usr_id` = 'A') CountA
  , SUM(`usr_id` = 'B') CountB
  , SUM(`usr_id` <> 'B' AND `usr_id` = 'B') CountElse
  
from myTable group by ts

Sample

And for posdgres

select 
  ts
  , SUM(CASE WHEN "usr_id" = 'A' THEN 1 ELSE 0 END) CountA
  , SUM(CASE WHEN "usr_id" = 'B' THEN 1 ELSE 0 END) CountB
  , SUM(CASE WHEN "usr_id" <> 'B' AND "usr_id" = 'B' THEN 1 ELSE 0 END) CountElse
  
  from myTable group by ts

see example