SELECT Year, results.dateID, CAST(AVG(entries) AS Integer) AS entries, CAST(AVG(eventPosition) AS Integer) AS position, AVG((100 / entries) * eventPosition) AS averagePosition, SUM(CASE WHEN eventPosition = 1 THEN 1 ELSE 0 END) AS first, SUM(CASE WHEN eventPosition = 2 THEN 1 ELSE 0 END) AS second, SUM(CASE WHEN eventPosition = 3 THEN 1 ELSE 0 END) AS third, SUM(CASE WHEN eventPosition > 3 AND eventPosition < 9 THEN 1 ELSE 0 END) AS last8, SUM(CASE WHEN eventPosition > 8 AND eventPosition < 17 THEN 1 ELSE 0 END) AS last16, SUM(CASE WHEN eventPosition > 16 AND eventPosition < 33 THEN 1 ELSE 0 END) AS last32, SUM(CASE WHEN eventPosition > 32 AND eventPosition < 65 THEN 1 ELSE 0 END) AS last64, SUM(CASE WHEN eventPosition > 64 AND eventPosition < 129 THEN 1 ELSE 0 END) AS last128 FROM results INNER JOIN eventData ON eventData.eventID = results.eventID AND results.dateID = eventData.dateID AND eventData.catID = results.eventCat INNER JOIN eventDates ON eventDates.eventID = eventData.eventID AND eventDates.ID = results.dateID WHERE results.eventID = 199 AND results.fencerClubID = 415 AND results.eventCat = 21 GROUP BY Year ORDER BYaveragePositionASC
epeeme
commented
4 years ago
Was 2020 GBRs best ever?
SELECT Year, results.dateID, CAST(AVG(entries) AS Integer) AS entries, CAST(AVG(eventPosition) AS Integer) AS position, AVG((100 / entries) * eventPosition) AS averagePosition, SUM(CASE WHEN eventPosition = 1 THEN 1 ELSE 0 END) AS first, SUM(CASE WHEN eventPosition = 2 THEN 1 ELSE 0 END) AS second, SUM(CASE WHEN eventPosition = 3 THEN 1 ELSE 0 END) AS third, SUM(CASE WHEN eventPosition > 3 AND eventPosition < 9 THEN 1 ELSE 0 END) AS last8, SUM(CASE WHEN eventPosition > 8 AND eventPosition < 17 THEN 1 ELSE 0 END) AS last16, SUM(CASE WHEN eventPosition > 16 AND eventPosition < 33 THEN 1 ELSE 0 END) AS last32, SUM(CASE WHEN eventPosition > 32 AND eventPosition < 65 THEN 1 ELSE 0 END) AS last64, SUM(CASE WHEN eventPosition > 64 AND eventPosition < 129 THEN 1 ELSE 0 END) AS last128 FROM results INNER JOIN eventData ON eventData.eventID = results.eventID AND results.dateID = eventData.dateID AND eventData.catID = results.eventCat INNER JOIN eventDates ON eventDates.eventID = eventData.eventID AND eventDates.ID = results.dateID WHERE results.eventID = 199 AND results.fencerClubID = 415 AND results.eventCat = 21 GROUP BY Year ORDER BYaveragePositionASC