0

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?

Jon Rodrigo
  • 35
  • 5
  • 1
    Does this answer your question https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql ? – Serg Feb 18 '22 at 09:20

2 Answers2

3

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;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 1
    `COUNT(CASE WHEN c.status NOT IN ('ABAN', 'DES', 'LET', 'NOAN') THEN 1 END) AS OTH` OP does not tell that the value in the column cannot be NULL. – Akina Feb 18 '22 at 09:24
1

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
sajjad rezaei
  • 945
  • 1
  • 10
  • 23