November 25, 2008 at 7:56 am
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
November 25, 2008 at 3:23 pm
Please refer to the link in my signature for how to post table structure / sample data for future questions. Doing so would have gotten you a tested answer. The below code is not tested, but should meet your requirements.
[font="Courier New"]SELECT DISTINCT
D.ohorseid,
RC.RaceCount,
WC.WinCount,
TPM.TotalPrizeMoney,
TPM.TotalPrizeMOney / (WinCount / RaceCount) PrizeMoneyCalc
FROM decouting D
LEFT JOIN (SELECT OHorseID, COUNT(*) RaceCount FROM outing GROUP BY OHorseID) RC
ON D.OHorseID = RC.OHorseID
LEFT JOIN (SELECT OHorseID, COUNT(*) WinCount FROM outing WHERE opos = 1 GROUP BY OhorseID) WC
ON D.OHorseID = WC.OHorseID
LEFT JOIN (SELECT OHorseID, SUM(b.rprize1) TotalPrizeMoney
FROM outing a
INNER JOIN race b ON a.oraceid = b.rid WHERE opos = '1') TPM
ON D.OHorseID = TPM.OHorseID[/font]
November 25, 2008 at 4:35 pm
Cheers Seth and thanks for the quick response
Apologies, I will read your post and give that a try to post some sample data although I am a novice at this stuff so bear with me. I did run your SQL after changing it a little but I am getting the following...any clues in the interim ?
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
November 25, 2008 at 5:01 pm
You are getting divide by Zero from your Horses with no Wins.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 25, 2008 at 5:03 pm
Yerp, change this:
TPM.TotalPrizeMOney / (WinCount / RaceCount)
To this:
CASE WHEN RaceCount = 0 THEN 0
WHEN WinCount = 0 THEN 0
ELSE TPM.TotalPrizeMOney / (WinCount / RaceCount)
END PrizeMoneyCalc
November 26, 2008 at 6:33 am
Thanks again Seth. I'm still hitting that divide by zero error because it looks like for 8 records there is not an rprize1 value for a few races for some reason. I tried putting in
WHEN TotalPrizeMoney = 0 THEN 0
at the top along with the others but it still is returning an error.
eg. this piece of sql from your code -:
SELECT ohorseid, SUM(b.rprize1) TotalPrizeMoney
FROM outing a
INNER JOIN race b ON a.oraceid = b.rid WHERE opos = '1' GROUP by ohorseid Order by TotalPrizeMoney
returns...
ohorseidTotalPrizeMoney
4838720.00
5111040.00
1007020.00
5855520.00
5744860.00
5470420.00
5302790.00
5018940.00
670919781.00
Also, the opos column is not numerical as it can contain "F" for fall, "P" for pulled up etc.
November 26, 2008 at 7:26 am
david.x.mcauley (11/26/2008)
Thanks again Seth. I'm still hitting that divide by zero error because it looks like for 8 records there is not an rprize1 value for a few races for some reason. I tried putting inWHEN TotalPrizeMoney = 0 THEN 0
at the top along with the others but it still is returning an error.
That should not be able to cause the "divide by zero" error: it can only happen when a denominator is zero and rprize1/TotalPrizeMoney is only ever used as a numerator. Seth's code looks right to me.
I suspect that either one of the tables that you are joining to is a view with a divisional calculation in it or one of the columns in the referenced tables is a calculated column with a division in it.
As a test, try this version of Seth's query, and show us the results for the rows that are failing now:
SELECT DISTINCT
D.ohorseid,
RC.RaceCount,
WC.WinCount,
TPM.TotalPrizeMoney,
WinCount,
RaceCount
FROM decouting D
LEFT JOIN (SELECT OHorseID, COUNT(*) RaceCount FROM outing GROUP BY OHorseID) RC
ON D.OHorseID = RC.OHorseID
LEFT JOIN (SELECT OHorseID, COUNT(*) WinCount FROM outing WHERE opos = '1' GROUP BY OhorseID) WC
ON D.OHorseID = WC.OHorseID
LEFT JOIN (SELECT OHorseID, SUM(b.rprize1) TotalPrizeMoney
FROM outing a
INNER JOIN race b ON a.oraceid = b.rid WHERE opos = '1') TPM
ON D.OHorseID = TPM.OHorseID
Notice there are no divisions here. If you still get "divide by zero" errors with this, then it is definitely coming from your data sources.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 26, 2008 at 7:34 am
This is what I get from running the sql -:
dohidRaceCountWinCountTotalPrizeMoneyWinCountRaceCount
30233991325319
4258832NULLNULLNULL2
45163482518728
51555161946465961
51711657730310757
524423115134334613115
531205104146631014104
54673871423386471
54862556514226556
5504929712345941297
55731759723327759
55745349417019449
55817649523317549
55982013410514413
5598318211480381182
5599944028536240
56250984891710884
562527648159668864
5631547111302761171
57082819530845519
5727038411475861184
57477236NULLNULLNULL36
57560139427200439
57635953531123553
57729532NULLNULLNULL32
57833453725521753
57945650512947550
5797463915010139
57985540634564640
57987675525379575
58062152648599652
5806517411302531174
58201745411260445
58222329421334429
5825426012193160
58262970726703770
58287850725704750
58381230418417430
5839968412304011284
58405275833772875
58464453619571653
58484751413392451
58867424NULLNULLNULL24
58973422517905522
58981519526626519
5928752427938224
59462923310562323
5947422115012121
59656185829994885
5971493413904134
59791820415210420
5982472413335124
5984552113409121
5990992827773228
59917360937207960
59955655922882955
59980616458864416
60122975411244475
60163733622582633
60269827315875327
60608033923731933
60743640416470440
6081842714202127
60855423528447523
60865723410946423
6086862313426123
6092971525087215
61586717211060217
61688019211109219
6207422013239120
6264322525287225
62657240317842340
62677139415825439
6316691828882218
63191471455417
6320621438405314
63419150515345550
6346652112048121
6355817NULLNULLNULL7
63596141537227541
63617046617027646
63640553635198653
6364411015205110
6364992712056127
63725155310920355
63733444413742444
6374163438594334
6375042526168225
63830313NULLNULLNULL13
63844226418299426
63858925413982425
63868125527776525
6389165NULLNULLNULL5
6391481724576217
63915031315454331
6393462828644228
64020136318150336
6402864939710349
64070353410247453
6413832229459222
6420612124096221
6422853237587332
64243216NULLNULLNULL16
64263747512528547
6434993036962330
64424418212361218
64431523311918323
64446127623338627
6454382412602124
64553735511435535
64839540612863640
6485253738494337
6491062529683225
6493181212590112
6493401514858115
6494392114534121
6497758NULLNULLNULL8
64991213315974313
6500988NULLNULLNULL8
6502663025645230
6504743328097233
6507001525560215
6510551138138311
6518227NULLNULLNULL7
6519202636143326
65208210NULLNULLNULL10
65221246412798446
6525231027807210
6528521811952118
6537472112602121
65440737311657337
6545932449890424
6557181728939217
65582626627784626
65641826411439426
65644743537834
6566231226180212
6575532713239127
6577832628093226
6581383236789332
65825738615550638
65829861390416
659060931086839
6597126NULLNULLNULL6
6597641738924317
65981531313601331
6611833029716230
6623691925129219
6624712413239124
6630091727423217
6631911214534112
66336912NULLNULLNULL12
6635812911911129
6639381325874213
6639421712193117
6639541412056114
6640221236006312
66415961341616
6645022113562121
66519252409425
66571420417165420
66609112210537212
66610614NULLNULLNULL14
66657114NULLNULLNULL14
66696911NULLNULLNULL11
667048631436136
66714212NULLNULLNULL12
6671552NULLNULLNULL2
6672701713239117
66765281260218
6681225NULLNULLNULL5
6685551712740117
6687221911911119
6687242112915121
66979212310212312
6710165NULLNULLNULL5
67111713NULLNULLNULL13
6716761638937316
6718763NULLNULLNULL3
67239631273013
67286691238819
6730153NULLNULLNULL3
6735777NULLNULLNULL7
6746477NULLNULLNULL7
6752275NULLNULLNULL5
6753162NULLNULLNULL2
6755668NULLNULLNULL8
6759042116070121
67595481296918
6764148NULLNULLNULL8
6767551536925315
67692128615156628
6776731NULLNULLNULL1
677733NULLNULLNULLNULLNULL
67779871204817
67800015NULLNULLNULL15
6783516NULLNULLNULL6
67881451420215
6790651424641214
6792671736482317
6798871215829112
6798992026153220
68051253762035
6805457NULLNULLNULL7
6805515NULLNULLNULL5
6806572011979120
6811201127448211
6814536NULLNULLNULL6
6815776NULLNULLNULL6
6821292224505222
68282018211010218
68381451582915
68381615NULLNULLNULL15
68383992816729
6838918NULLNULLNULL8
6841981314858113
6842296NULLNULLNULL6
68429714NULLNULLNULL14
68436193790039
6843631523822215
6845191214210112
68480724NULLNULLNULL24
6848949NULLNULLNULL9
6849036NULLNULLNULL6
68505851233115
6852351313071113
6854248NULLNULLNULL8
6861777NULLNULLNULL7
6866897NULLNULLNULL7
6867902NULLNULLNULL2
6879981113238111
68832491233119
6884171312457113
6890164NULLNULLNULL4
6890271112048111
68928511NULLNULLNULL11
69113851291515
6915938NULLNULLNULL8
69215651307115
69218442301424
6924209NULLNULLNULL9
6926964NULLNULLNULL4
6930645NULLNULLNULL5
6933731NULLNULLNULL1
69337515312521315
6936995NULLNULLNULL5
693964NULLNULLNULLNULLNULL
6942125NULLNULLNULL5
6942234NULLNULLNULL4
69443641238814
6945812NULLNULLNULL2
69506011137011
6958101NULLNULLNULL1
6960765NULLNULLNULL5
69654442422924
6965514NULLNULLNULL4
6966176NULLNULLNULL6
6966253NULLNULLNULL3
6976187NULLNULLNULL7
6982792NULLNULLNULL2
6983961NULLNULLNULL1
69921451214715
6994153NULLNULLNULL3
69956751273015
7000122NULLNULLNULL2
7000533NULLNULLNULL3
7000651NULLNULLNULL1
700402NULLNULLNULLNULLNULL
7012361NULLNULLNULL1
7019331NULLNULLNULL1
7024212NULLNULLNULL2
70247731171313
7024842NULLNULLNULL2
70316013312862313
7031672NULLNULLNULL2
7037071NULLNULLNULL1
70419691259019
70444292362729
704526NULLNULLNULLNULLNULL
7050161NULLNULLNULL1
7050352NULLNULLNULL2
7056023NULLNULLNULL3
7063617NULLNULLNULL7
7070801211979112
70723821227712
7075195NULLNULLNULL5
70848521518112
7089814NULLNULLNULL4
7094893NULLNULLNULL3
7104567NULLNULLNULL7
71059741427414
7108482NULLNULLNULL2
7113272NULLNULLNULL2
711421NULLNULLNULLNULLNULL
71230941556914
712743NULLNULLNULLNULLNULL
7127472NULLNULLNULL2
7137533NULLNULLNULL3
7148412NULLNULLNULL2
71539341262714
7153943NULLNULLNULL3
7157931NULLNULLNULL1
7168022NULLNULLNULL2
7169072NULLNULLNULL2
7170181NULLNULLNULL1
7171202NULLNULLNULL2
7173212NULLNULLNULL2
718087NULLNULLNULLNULLNULL
7181462NULLNULLNULL2
7181511NULLNULLNULL1
7184551NULLNULLNULL1
7185492NULLNULLNULL2
7188601NULLNULLNULL1
7193292NULLNULLNULL2
7197891NULLNULLNULL1
7200391NULLNULLNULL1
7204821NULLNULLNULL1
7205231NULLNULLNULL1
7206841NULLNULLNULL1
720895NULLNULLNULLNULLNULL
720954NULLNULLNULLNULLNULL
720960NULLNULLNULLNULLNULL
720962NULLNULLNULLNULLNULL
720963NULLNULLNULLNULLNULL
7209681NULLNULLNULL1
721048NULLNULLNULLNULLNULL
721052NULLNULLNULLNULLNULL
721391NULLNULLNULLNULLNULL
721472NULLNULLNULLNULLNULL
721475NULLNULLNULLNULLNULL
721591NULLNULLNULLNULLNULL
721592NULLNULLNULLNULLNULL
721593NULLNULLNULLNULLNULL
721594NULLNULLNULLNULLNULL
721596NULLNULLNULLNULLNULL
721717NULLNULLNULLNULLNULL
November 26, 2008 at 7:35 am
LEFT JOIN (SELECT OHorseID, SUM(b.rprize1) TotalPrizeMoney
FROM outing a
INNER JOIN race b ON a.oraceid = b.rid WHERE opos = '1') TPM
ON D.OHorseID = TPM.OHorseID
Should be
LEFT JOIN (SELECT OHorseID, SUM(b.rprize1) TotalPrizeMoney
FROM outing a
INNER JOIN race b ON a.oraceid = b.rid WHERE opos = '1'
GROUP BY OHorseID) TPM
ON D.OHorseID = TPM.OHorseID
I saw it in my query yesterday but assumed the OP had already fixed it.
November 26, 2008 at 7:39 am
Seth: Just for clarity, could you post the whole query again, with all of the modifications?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 26, 2008 at 7:45 am
This is the full query I have been running....
SELECT DISTINCT
D.dohid,
RC.RaceCount,
WC.WinCount,
TPM.TotalPrizeMoney,
CASE WHEN RaceCount IS NULL THEN 0
WHEN WinCount IS NULL THEN 0
WHEN TotalPrizeMoney IS NULL THEN 0
ELSE TPM.TotalPrizeMOney / (WinCount / RaceCount)
END PrizeMoneyCalc
FROM decouting D
LEFT JOIN (SELECT ohorseid, COUNT(*) RaceCount FROM outing GROUP BY ohorseid) RC
ON D.dohid = RC.ohorseid
LEFT JOIN (SELECT ohorseid, COUNT(*) WinCount FROM outing WHERE opos = '1' GROUP BY ohorseid) WC
ON D.dohid = WC.ohorseid
LEFT JOIN (SELECT ohorseid, SUM(b.rprize1) TotalPrizeMoney
FROM outing a
INNER JOIN race b ON a.oraceid = b.rid WHERE opos = '1' GROUP by ohorseid) TPM
ON D.dohid = TPM.ohorseid order by TotalPrizeMoney desc
November 26, 2008 at 7:53 am
Good call Barry.
[font="Courier New"]SELECT DISTINCT
D.Ohorseid,
RC.RaceCount,
WC.WinCount,
TPM.TotalPrizeMoney,
CASE WHEN ISNULL(RC.RaceCount,0) = 0 THEN 0
WHEN ISNULL(WC.WinCount,0) = 0 THEN 0
ELSE TPM.TotalPrizeMOney / (WC.WinCount / RC.RaceCount)
END PrizeMoneyCalc
FROM decouting D
LEFT JOIN (SELECT OHorseID, COUNT(*) RaceCount FROM outing GROUP BY OHorseID) RC
ON D.OHorseID = RC.OHorseID
LEFT JOIN (SELECT OHorseID, COUNT(*) WinCount FROM outing WHERE opos = '1' GROUP BY OhorseID) WC
ON D.OHorseID = WC.OHorseID
LEFT JOIN (SELECT OHorseID, SUM(b.rprize1) TotalPrizeMoney
FROM outing a
INNER JOIN race b ON a.oraceid = b.rid
WHERE opos = '1'
GROUP BY OHorseID) TPM
ON D.OHorseID = TPM.OHorseID[/font]
November 26, 2008 at 7:53 am
Well there's your problem: you changed Seth's Zero-checking CASE function into a Null-checking function. That still let's the Zero's through to the division, thus the error. Assuming that you want both, change it like this:
SELECT DISTINCT
D.dohid,
RC.RaceCount,
WC.WinCount,
TPM.TotalPrizeMoney,
CASE WHEN RaceCount IS NULL THEN 0
WHEN WinCount IS NULL THEN 0
WHEN TotalPrizeMoney IS NULL THEN 0
WHEN WinCount IS 0 THEN 0
WHEN WinCount IS 0 THEN 0
ELSE TPM.TotalPrizeMOney / (WinCount / RaceCount)
END PrizeMoneyCalc
FROM decouting D
LEFT JOIN (SELECT ohorseid, COUNT(*) RaceCount FROM outing GROUP BY ohorseid) RC
ON D.dohid = RC.ohorseid
LEFT JOIN (SELECT ohorseid, COUNT(*) WinCount FROM outing WHERE opos = '1' GROUP BY ohorseid) WC
ON D.dohid = WC.ohorseid
LEFT JOIN (SELECT ohorseid, SUM(b.rprize1) TotalPrizeMoney
FROM outing a
INNER JOIN race b ON a.oraceid = b.rid WHERE opos = '1' GROUP by ohorseid) TPM
ON D.dohid = TPM.ohorseid order by TotalPrizeMoney desc
Note that I have not incorporated Seth's latest changes.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 26, 2008 at 7:55 am
Oops, Seth beat me to it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 26, 2008 at 8:37 am
I'm afraid I'm still getting divide by zero on the new sql as well -:
SELECT DISTINCT
D.dohid,
RC.RaceCount,
WC.WinCount,
TPM.TotalPrizeMoney,
CASE WHEN ISNULL(RC.RaceCount,0) = 0 THEN 0
WHEN ISNULL(WC.WinCount,0) = 0 THEN 0
ELSE TPM.TotalPrizeMOney / (WC.WinCount / RC.RaceCount)
END PrizeMoneyCalc
FROM decouting D
LEFT JOIN (SELECT OHorseID, COUNT(*) RaceCount FROM outing GROUP BY OHorseID) RC
ON D.dohid = RC.OHorseID
LEFT JOIN (SELECT OHorseID, COUNT(*) WinCount FROM outing WHERE opos = '1' GROUP BY OhorseID) WC
ON D.dohid = WC.OHorseID
LEFT JOIN (SELECT OHorseID, SUM(b.rprize1) TotalPrizeMoney
FROM outing a
INNER JOIN race b ON a.oraceid = b.rid
WHERE opos = '1'
GROUP BY OHorseID) TPM
ON D.dohid = TPM.OHorseID
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply