How to check the count of each values repeating in a row-CodePudding

I have two tables. Data in the first table is:

ID	Username
1	Dan
2	Eli
3	Sean
4	John

Second Table Data:

user_id	Status_id
1	2
1	3
4	1
3	2
2	3
1	1
3	3
3	3
3	3
.	.
goes on	goes on

These are my both tables.

I want to find the frequency of individual users doing 'status_id'

My expected result is:

username	status_id(1)	status_id(2)	status_id(3)
Dan	1	1	1
Eli	0	0	1
Sean	0	1	2
John	1	0	0

My current code is:

SELECT b.username , COUNT(a.status_id)
FROM masterdb.auth_user b
left outer join masterdb.xmlform_joblist a
on a.user1_id = b.id 
GROUP BY b.username, b.id, a.status_id

This gives me the separate count but in a single row without mentioning which status_id each column represents

CodePudding user response：

This is called pivot and it works in two steps:

extracts the data for the specific field using a CASE statement
aggregates the data on users, to make every field value lie on the same record for each user

SELECT Username, 
       SUM(CASE WHEN status_id = 1 THEN 1 END) AS status_id_1,
       SUM(CASE WHEN status_id = 2 THEN 1 END) AS status_id_2,
       SUM(CASE WHEN status_id = 3 THEN 1 END) AS status_id_3
FROM       t2
INNER JOIN t1 
        ON t2.user_id = t1._ID
GROUP BY Username
ORDER BY Username

Check the demo here.

Note: This solution assumes that there are 3 status_id values. If you need to generalize on the amount of status ids, you would require a dynamic query. In any case, it's better to avoid dynamic queries if you can.