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
SELECT user_id, COUNT(color)
FROM cars
WHERE color = 'red'
GROUP BY user_id
1 | 2
2 | 1
3 | 1
Users with 1 red car : 2
Users with 2 red car : 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;