How do I write an SQL subquery to display additional columns?-CodePudding

I'm trying to find the 8 most common electric vehicles in King County. The following code gives me the correct results, but I also want it to display the Make and Model along with the count. I think this requires a subquery but I can't figure out how to write it.

The dataset can be found here: https://data.wa.gov/Transportation/Electric-Vehicle-Population-Data/f6w7-q2d2

SELECT COUNT(Model)
FROM electric_vehicle_population_data
WHERE County = 'King'
GROUP BY Model
ORDER BY COUNT(Model) DESC
LIMIT 8;

I tried the following subquery, which seemed close, but it doesn’t display the count:

SELECT Make, Model FROM electric_vehicle_population_data
WHERE Model IN (
    SELECT Model
    FROM electric_vehicle_population_data
    WHERE County = 'King'
    GROUP BY Model
    ORDER BY COUNT(Model) DESC)
    LIMIT 8;

CodePudding user response：

Join the table with the subquery that gets the counts.

SELECT t1.Model, t1.Make, t2.count
FROM electric_vehicle_population_data AS t1
JOIN (
    SELECT Model, COUNT(*) AS count
    FROM electric_vehicle_population_data
    WHERE County = 'King'
    GROUP BY Model
    ORDER BY count DESC
    LIMIT 8
) AS t2 ON t1.Model = t2.Model
WHERE t1.County = 'King'

CodePudding user response：

SELECT Make, Model, COUNT(*)
FROM electric_vehicle_population_data
WHERE County = 'King'
GROUP BY Make, Model
ORDER BY COUNT(*) DESC
LIMIT 8