MySQL – Get counts of `car models` by `car make`

countMySQLpivotqueryselect

I have a table with cars data, i.e. Makes, Models, & Year.

Car data with its models & years

I want to get the count for each Car with how many models it has… something like the image below:

Makes with model count

This is the SQL query I am using but it is giving me wrong counts. In fact, the number of rows against each car make is actually giving me my desired results.

SELECT `make`, COUNT(`id`) as `countmake`
FROM `cars_new`
WHERE 1
AND `make` IN ('Chrysler', 'Ferrari', 'Citroen', 'Dodge')
GROUP BY `model`

Help me to fix this!

Best Answer

You have to GROUP BY both make and model, to get the count

SELECT `make`,`model`, COUNT(*) as `countmakemodel`
FROM `cars_new`
WHERE `make` IN ('Chrysler', 'Ferrari', 'Citroen', 'Dodge')
GROUP BY `make`,`model`
Related Question