[Solved] Aggregate of subquery

Creating tables and queries
Post Reply
StormWolf
Posts: 12
Joined: Sun Jul 08, 2018 12:46 pm

[Solved] Aggregate of subquery

Post by StormWolf »

Thanks to Villeroy and FJCC the query to change a position number to a pts number is

SELECT "Pos", CASE WHEN "Pos">11 then 0 else 11-"Pos" END as "Pts" from "Result"

The result comes up as
Pts Driver
10 Driver1
10 Driver1
9 Driver2
9 Driver3
etc

From here I'd like to aggregate the Pts per Driver
Pts Driver
20 Driver1
17 Driver2
etc

I've tried a few varieties of Select Sum (around the subquery) with Group By "Driver" (and without) at the end of the statement but always get error msg. Difficult to know where i'm wrong if it just says "Syntax error in SQL expression" unfortunately.
Would love to know what i'm doing incorrectly.
Last edited by robleyd on Mon Dec 07, 2020 12:44 am, edited 1 time in total.
Reason: Tagged [Solved]
Open Office 4.1.3 Using Windows 10
StormWolf
Posts: 12
Joined: Sun Jul 08, 2018 12:46 pm

Re: Aggregate of subquery

Post by StormWolf »

SELECT SUM( CASE WHEN "Posn" > 11 THEN 0 ELSE 11 - "Posn" END ) AS "Pts", "TblDriver"."Driver" FROM "TblJoin", "TblDriver", "TblRaces", "TblVehicle" WHERE "TblJoin"."DriverID" = "TblDriver"."DriverID" AND "TblRaces"."RacesID" = "TblVehicle"."VehicleID" AND "TblJoin"."VehicleID" = "TblVehicle"."VehicleID" GROUP BY "TblDriver"."Driver" Order by "Pts" DESC

Works exactly as i wanted.
Open Office 4.1.3 Using Windows 10
Post Reply