Find the sum total of two CASE statements – (How to use a CASE expression or Lateral JOIN

I am looking to find the total profit made on an each way horse racing bet

I have created a view to show odds and returns for each part of the bet, but I would like to add another column (EachWayProfit) to find the total profit. I have tried to simply SUM the two case statements though receiving a Syntax error.

I include my code and output below,

ALTER VIEW EachWayBetting
AS

SELECT rc.ID, rc.RaceDate, rc.Runners,
       t.NAME AS Track, h.NAME as HorseName, 
       rc.place as FinishingPosition, 
       rc.IndustrySP as WinOdds,
       (rc.IndustrySP-1) / 5 as PlaceOdds,


 -- // calculates profit individually on the win & place parts of an each way bet with 1/5 place terms //
  
(CASE WHEN TRY_CONVERT(int, rc.Place) = 1 THEN (rc.IndustrySP - 1.0) ELSE -1 END) AS WinProfit,
(CASE WHEN TRY_CONVERT(int, rc.Place) <= 6 THEN (rc.IndustrySP - 1.0) / 5 ELSE -1 END) AS PlaceProfit

FROM dbo.NewRaceResult rc LEFT JOIN
   track t
   ON t.ID = rc.TrackID LEFT JOIN
   horse h
   ON h.ID = rc.HorseID
WHERE rc.Runners > 20 

This Returns:

I want to add the total returns from both the Win & Place Parts of the bet into a new column – EachWayProfit

How do I to structure this question?

-- // calculates total profit on the win & place parts the each way bet //
  
  CASE SUM(
  (CASE WHEN TRY_CONVERT(int, rc.Place) = 1 THEN (rc.IndustrySP - 1.0) ELSE -1 END)  
  +  
  (CASE WHEN TRY_CONVERT(int, rc.Place) <= 6 THEN (rc.IndustrySP - 1.0) / 5 ELSE -1 END)
  ) AS EachWayProfit

Answer

If you want to compute the sum of both values on the same row, then you typically need to repeat the conditional expressions, so something like:

CASE WHEN TRY_CONVERT(int, rc.Place) = 1 THEN (rc.IndustrySP - 1.0) ELSE -1 END
+ CASE WHEN TRY_CONVERT(int, rc.Place) <= 6 THEN (rc.IndustrySP - 1.0) / 5 ELSE -1 END as EachWWayProfit

In SQL Server, a lateral join comes handy to avoid redondancy:

ALTER VIEW EachWayBetting AS
SELECT 
    rc.ID, 
    rc.RaceDate, 
    rc.Runners, 
    t.name AS Track, 
    h.name as HorseName, 
    rc.place as FinishingPosition, 
    rc.IndustrySP as WinOdds, 
    (rc.IndustrySP-1) / 5 as PlaceOdds,
    pr.WinProfit,
    pr.PlaceProfitn
    pr.WinProfit - pr.PlaceProfit EachWWayProfit
FROM dbo.NewRaceResult rc 
LEFT JOIN track t ON t.ID = rc.TrackID 
LEFT JOIN horse h ON h.ID = rc.HorseID
CROSS APPLY (VALUES (
    CASE WHEN TRY_CONVERT(int, rc.Place) = 1 THEN (rc.IndustrySP - 1.0) ELSE -1 END,
    CASE WHEN TRY_CONVERT(int, rc.Place) <= 6 THEN (rc.IndustrySP - 1.0) / 5 ELSE -1 END
) pr(WinProfit, PlaceProfit)
WHERE rc.Runners > 20