SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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');
6 rows affected

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


user_id
denied_count
permitted_count
limited_count


25
1
1
0

139
1
0
3


select user_id,
       case when permission = 'Denied' then count(permission) else 0 end as denied_count,
       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
  from permissions
 group
    by user_id
Msg 8120 Level 16 State 1 Line 2
Column 'permissions.permission' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.