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.
[Solved] Aggregate of subquery
[Solved] Aggregate of subquery
Last edited by robleyd on Mon Dec 07, 2020 12:44 am, edited 1 time in total.
Reason: Tagged [Solved]
Reason: Tagged [Solved]
Open Office 4.1.3 Using Windows 10
Re: Aggregate of subquery
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.
Works exactly as i wanted.
Open Office 4.1.3 Using Windows 10