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

Question

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

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

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!

Answer 1

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`

Answer 2

So, I have found a solution to my problem. I am not sure if this is valid but it works for me.

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

This gives me required results.

I hope it helps someone else also.