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, 2020 at 18:09
  • Whar happened to "D"? Why does "A" have nb with a value of 2, not 1? Apr 5, 2020 at 18:15
  • Sorry !! edited
    – Jibeji
    Apr 5, 2020 at 18:17

1 Answer 1

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, 2020 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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