tijn167 tijn167 - 18 days ago 6
MySQL Question

Need to know the right syntax of a count query to count rows by id

I got these two tables and I want to have a query to count the amount of cars by each brand and insert this count to a column in the brand table
I've tried many queries but can't get it right.

First table,

enter image description here

Second table,

enter image description here

Answer

Use JOIN.

Query

select t1.car_brand_id, t2.brand_name, count(t1.car_name) as total_count
from table1 t1
join table2 t2
on t1.car_brand_id = t2.brand_id
group by t1.car_brand_id, t2.brand_name;