Count occurrences of a column value efficiently in SQL?-CodePudding

I have a Car table

id   | make   | model
--------
1000 | Toyota | Cammry
1001 | Toyota | Harrier
1002 | Honda  | Accord
1003 | Honda  | Civic

I have a Driver table

id   | name| car
--------
2000    Finch   1000
2001    Shin    1001
2002    Edward  1003
2003    Qing    1001
2004    Prasad  1002

I need write a single SQL query to find out how many drivers are there for each vehicle? (result must have both vehicle make & model and number of drivers)

CodePudding user response：

SELECT Car.make, Car.model, COUNT(Driver.id) AS drivers_count
FROM Driver
JOIN Car
ON Driver.car = Car.id
GROUP BY Car.make, Car.model

That's a single query. About performance, it looks perfectly fine to me, maybe you could explain us more in depth what's on your mind ?

CodePudding user response：

This would be the query:

SELECT
    COUNT( Driver.id ),
    model,
    make 
FROM
    Car
    LEFT JOIN Driver ON Car.id = Driver.car 
GROUP BY
    Car.model

You must have indexes on the LEFT JOIN columns and you will have a good performance.