How to count how many having how many in mysql-CodePudding

I have persons i one table [person] and cars [car] registered to each person i another, connected by id.

I want to get a list of how many persons having how many cars.

Something like this:

PERSON

id	name
1	LISA
2	ADAM
3	RAY

CARS

id	id_person	brand
1	3	FORD
2	1	BMW
3	2	VOLVO
4	1	VOLVO
5	1	VW

RESULT

no cars	no persons
1	2
2	0
3	1

Just dont get how to do it?

CodePudding user response：

You need to do it in two stages. The first is to get the number of cars per person -

SELECT p.id, COUNT(*) num_cars
FROM person p
JOIN cars c ON p.id = c.id_person
GROUP BY p.id

This can then be nested to count people per count -

SELECT num_cars, COUNT(*) num_people
FROM (
    SELECT p.id, COUNT(*) num_cars
    FROM person p
    JOIN cars c ON p.id = c.id_person
    GROUP BY p.id
) t
GROUP BY num_cars
ORDER BY num_cars ASC