CODEWITHSUNDEEP
sql
Dallas
Dallas

Reputation: 35

SQL - Writing Cumulative Totals

I am building a horse racing database and have calculated WinReturn for each race.

I would like to add a column for cumulative returns for each horse but I dont think I can sum WinReturn as this column is calculated on the fly.

I have tried the following but am receiving either syntax errors or it is simply calculating the number of times as horse has raced:

(SELECT COUNT(*) FROM raceresult WHERE HorseId = A.HorseId AND RaceDate <= A.RaceDate IF (A.Place = 1, 10.0 * (A.BFSP-1), -10) AS CumulativeWinReturn,

Can anyone see where I am going wrong?

       SELECT
       horse.Name AS Horse,
       country.Name AS Country,

       -- DATE(raceresult.RaceDate) AS RaceDate,
       DATE_FORMAT(A.RaceDate,'%d/%m/%Y') AS RaceDate,

       -- TIME(RaceDate) AS Time,
       TIME_FORMAT(A.RaceDate,'%k:%i') AS `Time`,   

       track.Name AS Track,
       going.Name AS Going,
       type.Name AS `Race Type`,
       distance.Name as Distance,
       A.Runners,
       jockey.Name AS Jockey,
       -- A.DistanceM,
       -- FORMAT(distance.Meters,2) as DistanceM,
       -- A.DistanceF,
       -- FORMAT(distance.Furlongs,2) as DistanceF,
       A.Class,
       -- A.OfficialRating,
       IF (A.OfficialRating IS NULL, '-', A.OfficialRating) as `OR`,
       A.Weight,
       -- FORMAT(A.WeightKg,2) as WeightKg,

       -- Ratings
       IF (A.HeadGearID IS NULL, '', headgear.Name) AS `Head Gear`,
       A.BFSP,
       A.Place,
       IF(A.Place = 1, FORMAT(10.0 * (A.BFSP-1), 2), -10) AS WinReturn,
       NULL AS CumulativeWinReturn,

       -- Summary Bar
       NULL AS `Summary Bar >`,
       A.Age,
       trainer.Name AS Trainer,

       IF ((SELECT COUNT(*) FROM raceresult WHERE HorseId = A.HorseId AND RaceDate < A.RaceDate)=0, 'None',DATEDIFF(A.RaceDate , (SELECT MAX(RaceDate) FROM raceresult WHERE HorseId = A.HorseId AND RaceDate < A.RaceDate))) AS `Last Run`,
       (SELECT COUNT(*) FROM raceresult WHERE HorseId = A.HorseId AND RaceDate <=A.RaceDate) AS Runs,
       (SELECT COUNT(*) FROM raceresult WHERE Place = '1' AND HorseId = A.HorseId AND RaceDate <= A.RaceDate) as Wins,
       (SELECT COUNT(*) FROM raceresult WHERE Place = '2' AND HorseId = A.HorseId AND RaceDate <= A.RaceDate) as `2NDS`,
       (SELECT COUNT(*) FROM raceresult WHERE Place = '3' AND HorseId = A.HorseId AND RaceDate <= A.RaceDate) as `3RDS`,
       FORMAT(100.0 * (SELECT COUNT(*) FROM raceresult WHERE Place = '1' AND HorseId = A.HorseId AND RaceDate <= A.RaceDate) / (SELECT COUNT(*) FROM raceresult WHERE HorseId = A.HorseId AND RaceDate <= A.RaceDate), 2) as `Win%`,
       -- Returns
       NULL AS Win,
       NULL AS `E/W`,
       NULL AS Place,
       FORMAT(IF(A.Place = 1, (10.0 * (A.BFSP-1) * -1), 9.8),2) AS Lay


FROM raceresult A
LEFT OUTER JOIN country ON country.ID = A.CountryId
LEFT OUTER JOIN track ON track.ID = A.TrackID
LEFT OUTER JOIN going ON going.ID = A.GoingID
LEFT OUTER JOIN type ON type.ID = A.TypeID
LEFT OUTER JOIN distance ON distance.ID = A.DistanceID
LEFT OUTER JOIN horse ON horse.ID = A.HorseID
LEFT OUTER JOIN jockey ON jockey.ID = A.JockeyID
LEFT OUTER JOIN headgear ON headgear.ID = A.HeadGearID
LEFT OUTER JOIN trainer ON trainer.ID = A.TrainerID
WHERE horse.NAME = 'THORPE'
ORDER BY A.RaceDate DESC

Upvotes: 0

Views: 83

Answers (2)

Dallas
Dallas

Reputation: 35

So I wasn't far off, I had 2 main errors:

1) I used COUNT where I should have used SUM

2) I put the WHERE CLAUSE before the formula

ORIGINAL INCORRECT FORMAT:

(SELECT COUNT(*) FROM raceresult WHERE HorseId = A.HorseId AND RaceDate <= A.RaceDate IF (A.Place = 1, 10.0 * (A.BFSP-1), -10) AS CumulativeWinReturn,

CORRECT FORMAT:

(SELECT SUM(IF(Place = 1, 10.0 * (BFSP-1), -10)) FROM raceresult WHERE HorseID=A.HorseID AND RaceDate <= A.RaceDate) as CumulativeWinReturn,

FORMATTED TO 2 DECIMAL PLACES:

FORMAT((SELECT SUM(IF(Place = 1, 10.0 * (BFSP-1), -10)) FROM raceresult WHERE HorseID=A.HorseID AND RaceDate <= A.RaceDate), 2) as CumulativeWinReturn,

Upvotes: 0

Marco A. Garcia
Marco A. Garcia

Reputation: 28

Here's the change you need to get the CumulativeWinReturn. Notes: 1) I added a format function, to keep it in accordance to the original calculation for WinReturn, and 2) -10 for races that were not won is kept as a numeric value (this is, it subtracts from the SUM, if you only want the cumulative for won races, the set it to 0 in the IF statement inside the SUM function

SELECT
       horse.Name AS Horse,
       country.Name AS Country,

       -- DATE(raceresult.RaceDate) AS RaceDate,
       DATE_FORMAT(A.RaceDate,'%d/%m/%Y') AS RaceDate,

       -- TIME(RaceDate) AS Time,
       TIME_FORMAT(A.RaceDate,'%k:%i') AS `Time`,   

       track.Name AS Track,
       going.Name AS Going,
       type.Name AS `Race Type`,
       distance.Name as Distance,
       A.Runners,
       jockey.Name AS Jockey,
       -- A.DistanceM,
       -- FORMAT(distance.Meters,2) as DistanceM,
       -- A.DistanceF,
       -- FORMAT(distance.Furlongs,2) as DistanceF,
       A.Class,
       -- A.OfficialRating,
       IF (A.OfficialRating IS NULL, '-', A.OfficialRating) as `OR`,
       A.Weight,
       -- FORMAT(A.WeightKg,2) as WeightKg,

       -- Ratings
       IF (A.HeadGearID IS NULL, '', headgear.Name) AS `Head Gear`,
       A.BFSP,
       A.Place,
       IF(A.Place = 1, FORMAT(10.0 * (A.BFSP-1), 2), -10) AS WinReturn,
       FORMAT((SELECT SUM(IF(Place = 1, 10.0 * (BFSP-1),  -10)) FROM raceresult WHERE HorseID=A.HorseID AND RaceDate <= A.RaceDate), 2) as CumulativeWinReturn,

       -- Summary Bar
       NULL AS `Summary Bar >`,
       A.Age,
       trainer.Name AS Trainer,

       IF ((SELECT COUNT(*) FROM raceresult WHERE HorseId = A.HorseId AND RaceDate < A.RaceDate)=0, 'None',DATEDIFF(A.RaceDate , (SELECT MAX(RaceDate) FROM raceresult WHERE HorseId = A.HorseId AND RaceDate < A.RaceDate))) AS `Last Run`,
       (SELECT COUNT(*) FROM raceresult WHERE HorseId = A.HorseId AND RaceDate <=A.RaceDate) AS Runs,
       (SELECT COUNT(*) FROM raceresult WHERE Place = '1' AND HorseId = A.HorseId AND RaceDate <= A.RaceDate) as Wins,
       (SELECT COUNT(*) FROM raceresult WHERE Place = '2' AND HorseId = A.HorseId AND RaceDate <= A.RaceDate) as `2NDS`,
       (SELECT COUNT(*) FROM raceresult WHERE Place = '3' AND HorseId = A.HorseId AND RaceDate <= A.RaceDate) as `3RDS`,
       FORMAT(100.0 * (SELECT COUNT(*) FROM raceresult WHERE Place = '1' AND HorseId = A.HorseId AND RaceDate <= A.RaceDate) / (SELECT COUNT(*) FROM raceresult WHERE HorseId = A.HorseId AND RaceDate <= A.RaceDate), 2) as `Win%`,
       -- Returns
       NULL AS Win,
       NULL AS `E/W`,
       NULL AS Place,
       FORMAT(IF(A.Place = 1, (10.0 * (A.BFSP-1) * -1), 9.8),2) AS Lay
FROM raceresult A
LEFT OUTER JOIN country ON country.ID = A.CountryId
LEFT OUTER JOIN track ON track.ID = A.TrackID
LEFT OUTER JOIN going ON going.ID = A.GoingID
LEFT OUTER JOIN type ON type.ID = A.TypeID
LEFT OUTER JOIN distance ON distance.ID = A.DistanceID
LEFT OUTER JOIN horse ON horse.ID = A.HorseID
LEFT OUTER JOIN jockey ON jockey.ID = A.JockeyID
LEFT OUTER JOIN headgear ON headgear.ID = A.HeadGearID
LEFT OUTER JOIN trainer ON trainer.ID = A.TrainerID
WHERE horse.NAME = 'THORPE'
ORDER BY A.RaceDate DESC

Upvotes: 1

Related Questions