0

I've got his table:

+----+------+---------+
| id | user | upgrade |  
+----+------+---------+
|  0 |   A  |   NULL  | 
+----+------+---------+
|  1 |   B  |   NULL  | 
+----+------+---------+
|  2 |   C  |    A    | 
+----+------+---------+
|  3 |   A  |   NULL  | 
+----+------+---------+

I would like with an unique query to know the total of occurence grouped by user_id but also to know the total of the upgrades by user.

Something like that:

+------+------+---------+
| user |  nb  | nb_upgd |  
+------+------+---------+
|  A   |   2  |    1    | 
+------+------+---------+
|  B   |   1  |    0    | 
+------+------+---------+
|  C   |   1  |    0    | 
+------+------+---------+

I have tried this with no luck:

SELECT user, COUNT(*) AS nb,
COUNT(CASE WHEN t.user_id=upgrade THEN 1 END) AS nb_upgd
FROM table t GROUP BY user
3
  • Where is user D lost?
    – Akina
    Apr 5 '20 at 18:09
  • Whar happened to "D"? Why does "A" have nb with a value of 2, not 1? Apr 5 '20 at 18:15
  • Sorry !! edited
    – Jibeji
    Apr 5 '20 at 18:17
1
SELECT user, MAX(nb) nb, MAX(nb_upgd) nb_upgd
FROM ( SELECT user,
              COUNT(user) nb,
              0 nb_upgd 
       FROM t
       GROUP BY user
     UNION ALL
       SELECT upgrade,
              0,
              COUNT(upgrade)
       FROM t
       GROUP BY upgrade
     ) total
WHERE user IS NOT NULL
GROUP BY user;

fiddle

1
  • Great, thank you!
    – Jibeji
    Apr 6 '20 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.