Your CTE is fine, all that needs to change is your final select.

SELECT MAX(an.id)...

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...

LEFT JOIN (SELECT OHorseID, SUM(b.rprize1) TotalPrizeMoney

FROM outing a

...

I get that. However, if you can show me a specific example of what you want to return, I can understand what you're trying to accomplish. Different metadata is...

All that lurking and you haven't seen us sending people to the same post over and over? :hehe: Please refer to the article in my signature for an example...

Could you be more specific? Providing a table with definition and exactly what you want to return from that table would help. See the link in my signature...

For the sample data provided, please give your expected results.

Are they:

10031832

100311592

100311602

100311612

800003925051

10031140012

10031800003920

?

Drop and recreate the table.

(You can use SSMS to generate a sql script for the table)

That said, the identity column is really there for uniqueness and the actual numbers shouldn't...

Yerp, change this:

TPM.TotalPrizeMOney / (WinCount / RaceCount)

To this:

CASE WHEN RaceCount = 0 THEN 0

WHEN WinCount = 0 THEN 0

...

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....

Are you running this through ASP?

When you make the modifications Lynn suggested, please do it in this thread rather than creating another one. Creating multiple threads on the same issue is confusing and annoys...

Please refer to the article in my signature on how to post sample data on this forum.

Grats on Carpal Lynn!

You just add in the extra handler:

Select Case @tst1 When 1 Then 'Yes' Else 'No' END ,

Case @tst2 When 1 Then 'Yes'...