November 26, 2008 at 8:41 am
Is that query the only thing that is in your query window? Sometimes in cases like this it's executing something else that you're not intending to execute which is causing your error. That code should not cause a divide by zero. Try opening a new query window and pasting just that into it.
November 26, 2008 at 9:08 am
I have checked and re-opened a new query window but same error is happening. I have run all 3 individual queries and the only 0 results are the 8 I mentioned for Prizemoney so it's a bit baffling.
November 26, 2008 at 9:10 am
Oh, wait, I think I've got it:
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 / CAST(RC.RaceCount as Float))
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
[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 9:20 am
Bingo ! That's great - thanks a lot guys, really appreciate your combined efforts. If it helps to start producing winners I'll throw a few tips your way 😉
November 26, 2008 at 9:25 am
@Barry:
UGH. I thought about rounding like 3 times, but kept looking at his data and thinking "It's all integers, shouldn't be an issue." Well, it's not. Until you divide them. Duh. :Whistling:
@OP:
Glad we could help.
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply