MySQL : count occurences on different columns in a same query 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
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