Select count group by another count (My)SQL

Question

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 :

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 1

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;

Select count group by another count (My)SQL

Sign up