CODEWITHSUNDEEP
mysqlsql
shjnlee
shjnlee

Reputation: 243

SQL: Group By & Having COUNT(*) with Subquery issue

Assume a database with schema

Write a SQL statement to list the names and driver-id of drivers who have taken trips to New York more times than they have taken trips to Washington.

I'm trying to express with something like

SELECT 
    name, driver-id 
FROM 
    DRIVER, TRIP,
    (SELECT name, driver-id 
     FROM DRIVER, TRIP 
     WHERE TRIP.driver-id = DRIVER.driver-id AND destination = “Washington” 
     GROUP BY name, driver-id) as TEMP 
WHERE
    TRIP.driver-id = DRIVER.driver-id 
    AND destination = “New York” 
    AND DRIVER.driver-id = TEMP.driver-id
GROUP BY 
    name, driver-id 
HAVING 
    COUNT(*) > TEMP.COUNT(*);

Is this correct? Thanks!

Upvotes: 0

Views: 374

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13006

use aggregation sum() and case keyword

select * from (
    SELECT d.name, d.driver-id
        , sum(case when destination = 'Washington' then 1 else 0 end) as washington
        , sum(case when destination = 'New York' then 1 else 0 end) as newyork
    FROM DRIVER d
    INNER JOIN TRIP t ON t.driver-id = d.driver-id
    GROUP BY d.name, d.driver-id) t1 
where t1.newyork > t1.washington

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

I think you only need to aggregate by driver here and then assert that the count of trips to New York exceeds the count of trips to Washington:

SELECT
    d.name,
    d.driverid
FROM DRIVER d
INNER JOIN TRIP t
    ON d.driverid = t.driverid
GROUP BY
    d.name,
    d.driverid
HAVING
    SUM(t.destination = 'New York') >
    SUM(t.destination = 'Washington');

This approach just makes a single pass over the joined tables using conditional aggregation for the two counts. I would not normally use your approach, because of the large subquery in the select clause.

Upvotes: 2

Related Questions