Grouping records by categorical criteria

Question

I came across the following problem - have table with this structure.

user_id	permission
25	Denied
25	Permitted
139	Denied
139	Limited
139	Limited
139	Limited

It is necessary to group records by categorical criteria (3 categories) into one line for "user_id", counting different states of "permission". It should look like this:

user_id	denied_count	limited_count	permitted_count
25	1	0	1
139	1	3	0

I tried to do grouping using group by and case with this script:

select user_id, 
       permission, 
       case when permission = 'PERMITTED' then count("permission") else 0 end as permitted_count,
       case when permission = 'LIMITED' then count("permission") else 0 end as limited_count,    
       case when permission = 'DENIED' then count("permission") else 0 end as denied_count                                       
                     from table
                     group by 1,2
                     order by user_id

but take record in this way:

user_id	denied_count	limited_count	permitted_count
25	1	0	0
25	0	0	1
139	1	0	0
139	0	3	0

please tell me where am I wrong.

Answer 1

I'm not very familiar with BigQuery. That syntax you are doing might be perfectly fine if you remove permission from the select and group by.

This is a pretty standard way to do it though:

https://dbfiddle.uk/liKLlSxH

CREATE TABLE permissions (
    user_id INT,
    permission VARCHAR(50)
);

INSERT INTO permissions (user_id, permission) VALUES (25, 'Denied');
INSERT INTO permissions (user_id, permission) VALUES (25, 'Permitted');
INSERT INTO permissions (user_id, permission) VALUES (139, 'Denied');
INSERT INTO permissions (user_id, permission) VALUES (139, 'Limited');
INSERT INTO permissions (user_id, permission) VALUES (139, 'Limited');
INSERT INTO permissions (user_id, permission) VALUES (139, 'Limited');

select user_id,
       sum(case when permission = 'Denied' then 1 else 0 end) as denied_count,
       sum(case when permission = 'Permitted' then 1 else 0 end) as permitted_count,
       sum(case when permission = 'Limited' then 1 else 0 end) as limited_count
  from permissions
 group
    by user_id;