I have two tables Users and Get_cal like below :
Users
id_user(pk) | com |
---|---|
1004 | 700 |
1005 | 700 |
1006 | 700 |
1010 | 701 |
1011 | 701 |
1012 | 701 |
1013 | 701 |
1014 | 800 |
1015 | 800 |
1016 | 800 |
1017 | 800 |
Get_cal
id_user(fk) | status |
---|---|
1004 | ABAN |
1004 | ABAN |
1004 | DES |
1004 | LET |
1004 | DES |
1004 | ABAN |
1011 | LET |
1011 | LET |
1011 | ABAN |
1015 | ABAN |
1015 | DES |
1015 | LET |
1015 | LET |
For status column I have 5 types (ABAN,DES,LET,NOANS,OTH). I want to get this result like below(Get the count by user by status ):
id_user | ABAN | DES | LET | NOANS | OTH |
---|---|---|---|---|---|
1004 | 3 | 2 | 1 | 0 | 0 |
1005 | 0 | 0 | 0 | 0 | 0 |
1006 | 0 | 0 | 0 | 0 | 0 |
1010 | 0 | 0 | 0 | 0 | 0 |
1011 | 1 | 0 | 2 | 0 | 0 |
1012 | 0 | 0 | 0 | 0 | 0 |
1013 | 0 | 0 | 0 | 0 | 0 |
1014 | 0 | 0 | 0 | 0 | 0 |
1015 | 1 | 1 | 2 | 0 | 0 |
1016 | 0 | 0 | 0 | 0 | 0 |
1017 | 0 | 0 | 0 | 0 | 0 |
I have no idea how to start the query, please any suggestion?
CodePudding user response:
You want conditional aggregation here:
SELECT
u.id_user,
COUNT(CASE WHEN c.status = 'ABAN' THEN 1 END) AS ABAN,
COUNT(CASE WHEN c.status = 'DES' THEN 1 END) AS DES,
COUNT(CASE WHEN c.status = 'LET' THEN 1 END) AS LET,
COUNT(CASE WHEN c.status = 'NOANS' THEN 1 END) AS NOANS,
COUNT(CASE WHEN c.status NOT IN ('ABAN', 'DES', 'LET', 'NOAN')
THEN 1 END) AS OTH
FROM Users u
LEFT JOIN Get_cal c
ON c.id_user = u.id_user
GROUP BY
u.id_user
ORDER BY
u.id_user;
CodePudding user response:
If your status is always static with that 5 cases. you can use left join
and group by
and some switch case conditions to solve the query.
SELECT Users.id_user ,
SUM(CASE WHEN Get_cal.status = 'ABAN' THEN 1 ELSE 0 END) AS ABAN ,
SUM(CASE WHEN Get_cal.status = 'DES' THEN 1 ELSE 0 END) AS DES ,
SUM(CASE WHEN Get_cal.status = 'LET' THEN 1 ELSE 0 END) AS LET ,
SUM(CASE WHEN Get_cal.status = 'NOANS' THEN 1 ELSE 0 END) AS NOANS ,
SUM(CASE WHEN Get_cal.status = 'OTH' THEN 1 ELSE 0 END) AS OTH
FROM Users LEFT JOIN Get_cal ON (Users.id_user = Get_cal.id_user) GROUP BY Users.id_user ,Get_cal.id_user