How to execute group by in another group by in mysql?-CodePudding

I have 2 tables: cars and lands:

cars (id, land_id)
lands (id, district_id, location)

I need to get count of cars per each district. What I have now:

SELECT district_id, COUNT(*) 
FROM lands 
GROUP BY district_id

But of course it returns only count of lands per each district. How to get count of cars?

Expected result:

district_id | cars_count
1 | 30
3 | 10
...

CodePudding user response：

The problem that you select only from lands table, when you also need to join cars table.

SELECT l.id, count(c.land_id)
FROM lands as l LEFT JOIN cars as c ON l.id = c.land_id
GROUP BY l.id

CodePudding user response：

left join these 2 tables based on land_id(cars) and id(lands)

select a.id,
       count(b.id) 
from lands a 
left join cars b on a.id=b.land_id 
group by 1;