user3198168 user3198168 - 6 months ago 6
SQL Question

Max/min/count etc. not giving good value

I have a f1

database
from
ergast.com
. I've tried to get best
laptime
and number of
pitstops
for each driver in each race, so I've written sql query like this:

SELECT results.driverId, results.constructorId, races.circuitId, races."year"-1949,
results.statusId, results.positionOrder, results.grid, results.points,
results.laps, ISNULL(max(pitstops.stop), 0), min(laptimes.milliseconds),
results.fastestLapTime, drivers.forename, drivers.surname, circuits.name
FROM results
INNER JOIN races
ON (results.raceId = races.raceId)
LEFT OUTER JOIN pitstops
ON (races.raceId = pitstops.raceId)
LEFT OUTER JOIN laptimes
ON (laptimes.raceId = races.raceId)
INNER JOIN drivers
ON (drivers.driverId = results.driverId)
INNER JOIN circuits
ON (circuits.circuitId = races.circuitId)
WHERE ((races."year"=2011) OR (races."year"=2012))
AND ((races.circuitId = 1) OR (races.circuitId = 2))
GROUP BY results.driverId, constructorId, races.circuitId, races."year"-1949,
statusId, positionOrder, grid, points, laps, fastestLapTime, forename,
surname, circuits.name


Unfortunately this gives me the same best
laptime
and number of
pitstops
for every driver in given race. How can I fix it? Should I add something to
group by
?

Answer

You are joining pitstops on raceId only, thus getting the maximum pitstop per race. But you say "and number of pitstops for every driver in given race". This indicates that a pitstop should be related to a driver, e.g.:

LEFT OUTER JOIN pitstops 
   ON (results.raceId = pitstops.raceId and results.driverId = pitstops.driverId)

The same should be true for laptimes.

(There may be more errors in your query. Please qualify all columns with a table name, if you want more help, so we see which columns is in which table. Even better would be table definitions.)

Comments