Carlos2W Carlos2W - 6 months ago 9
SQL Question

Select count group by another count (My)SQL

Let's say I have a table that lists cars by user.

id | user_id | color
1 | 1 | red
2 | 1 | red
3 | 2 | blue
4 | 2 | red
5 | 3 | red


Now, I want to know how much red cars each client has, so I've done this SQL :

SELECT user_id, COUNT(color)
FROM cars
WHERE color = 'red'
GROUP BY user_id


Which lists me :

1 | 2
2 | 1
3 | 1


But what I really want is the count of each count. Something like :

Users with 1 red car : 2
Users with 2 red car : 1
...


So is there a way to count my select which already includes a count() grouped by ?

Thank you in advance !

Answer

Use an aggregation of aggregations:

SELECT redCount, COUNT(*)
FROM (SELECT user_id, COUNT(color) as redCount
      FROM cars
      WHERE color = 'red'
      GROUP BY user_id
     ) uc
GROUP BY redCount;
Comments