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`
AND `make` IN ('Chrysler', 'Ferrari', 'Citroen', 'Dodge')
GROUP BY `model`

Help me to fix this!

  • Provide sample source data as CREATE TABLE + INSERT NITO scripts. Provide3 desired output for provided data.
    – Akina
    Nov 8, 2020 at 10:15
  • Show us what you would like the output to look like.
    – Rick James
    Nov 8, 2020 at 23:47

2 Answers 2


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`

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`
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.

Cars by Models

I hope it helps someone else also.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.