Greetings, I'm struggling to get this Jet SQL query to tell me how many race wins a driver has on different tracks

35 Views Asked by At

Here's the SQL:

SELECT  
    SessionResults.Driver, 
    COUNT(IIF(SessionResults.Place=1, 1, NULL)) AS Wins,  
    ROUND(AVG([SessionResults].[Place]), 2) AS [Avg Race Placing],  
    ROUND(AVG([SegmentData].[SegPlace]),2) AS [Avg Heat Placing], 
    COUNT(SessionResults.TotalLaps) AS [Total Laps], 
    EventHistory.TrackID AS [Track/Layout], 
    MAX(EventHistory.Date) AS [Last Race]
FROM 
    SessionResults, SegmentData, EventHistory
WHERE 
    (((SessionResults.EventID) = [EventHistory].[EventID]) 
      AND ((SegmentData.EventID) = [SessionResults].[EventID]))
GROUP BY 
    SessionResults.Driver, EventHistory.TrackID;

I've tried all kinds of different approaches but the second expression in the select never produces the right results. Does anybody know how one can count the SessionResults.Place columns that have a value of 1?

I've tried all kinds of methods. I've been able to count in that position other column values accidently, I've counted negative numbers, and completely inaccurate numbers. I can do a specific select and get the right data, just not in a complex Select statement like above.

0

There are 0 best solutions below