question regarding horse racing database query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

question regarding horse racing database query

Author

Topic

secretg
Starting Member

1 Post

Posted - 2008-11-25 : 09:58:36

I have a horse racing database and want to interrogate it to get a certain set of data out of it but my SQL is pretty average so I am hoping someone can help me. What I need to do is basically need to calculate is Total Prizemoney a horse has won divided by (Horses Total Wins divided by Horses Total Runs). I have the 3 separate pieces of SQL below but just need to know how to link them all into one sql query and also if possible how to loop through each runner from the decouting table to get this info for all runners in a day.

select count(*) from outing where ohorseid = 684361;
This gives me a figure of 9 total races the horse has had.

select count(*) from outing where ohorseid = 684361 and opos = '1';
This gives me the horses total amount of wins

select sum(b.rprize1) from outing a INNER JOIN race b on a.oraceid = b.rid where ohorseid = 684361 and opos = '1' ;
Finally, this gives me the horses total win prizemoney

The tables are as follows -:

decrace - contains the days races
decouting - contains all the days runners
race - contains details of past races
outing - contains details of a horses previous races

This is as far as I have got with some of my sql which displays all the data I need to work with I think, I just don't know how to loop through each runner in decouting from all the races in decrace using the various data in race and outing tables for historical information...if anyone can help I would appreciate it.

select a.drid,a.drdate,a.drcname,a.drtime,a.drbetting,
b.doraceid,b.dohid,b.dosaddle,b.dohname,b.dodaysince,b.docrsdist,b.doage,b.dojc,b.domasterlb,b.domstspd, c.ohorseid,c.oraceid,c.opos,c.orf,c.ospeed,
d.rid,d.rprize1,d.rclass,d.rinfocus from decrace a
INNER JOIN
decouting b
ON a.drid = b.doraceid
INNER JOIN
outing c
on b.dohid = c.ohorseid
INNER JOIN
race d
on c.oraceid = d.rid

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 10:11:12

[code]SELECT x.oHorseID,
SUM(CASE WHEN x.oPos = '1' THEN 1 ELSE 0 END) AS Wins,
COUNT(*) AS Races
SUM(CASE WHEN x.oPos = '1' THEN r.rPrize1 ELSE 0 END) AS Price
FROM Outing AS x
LEFT JOIN Race AS r ON r.rID = x.oRaceID
GROUP BY x.oHorseID[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources