MySQL : count occurences on different columns in a same query

Question

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

Whar happened to "D"? Why does "A" have nb with a value of 2, not 1? — Michael Kutz
– Michael Kutz, Commented Apr 5, 2020 at 18:15

Akina · Accepted Answer · 2020-04-05 21:02:08Z

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

edited Apr 5, 2020 at 21:02

answered Apr 5, 2020 at 20:07

Akina

20.8k2 gold badges20 silver badges22 bronze badges

Add a comment |

Stack Exchange Network

MySQL : count occurences on different columns in a same query

1 Answer 1

Your Answer

Hot Network Questions

MySQL : count occurences on different columns in a same query

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions