SQL Query with CASE inside SUM not working

user_5803584 0
Viewed 269

A shortened query:

select t.Docket, t.TranType,CAST(ROUND(t.Net, 2) AS decimal(18,2)) as Net1,
CASE
    WHEN t.TranType = 'SRT'  THEN CAST(ROUND(t.Net, 2) AS decimal(18,2))  * -1
    WHEN t.TranType = 'DIV'  THEN t.net  * 0
    ELSE CAST(ROUND(t.Net, 2) AS decimal(18,2)) 
END AS Net2
                from trans t

enter image description here

But when I want to add:

SUM (CASE
    WHEN t.TranType = 'SRT'  THEN CAST(ROUND(t.Net, 2) AS decimal(18,2))  * -1
    WHEN t.TranType = 'DIV'  THEN t.net  * 0
    ELSE CAST(ROUND(t.Net, 2) AS decimal(18,2)) 
END AS Net3) OVER () AS Total

I get error: Incorrect syntax near 'SUM'. I just want a total amount adding up everything in Net2

sql
1 Answers

You have a column alias in the SUM() expression. You just want:

SUM(CASE WHEN t.TranType = 'SRT'  THEN CAST(ROUND(t.Net, 2) AS decimal(18,2))  * -1
         WHEN t.TranType = 'DIV'  THEN t.net  * 0
         ELSE CAST(ROUND(t.Net, 2) AS decimal(18,2)) 
     END) OVER () AS Total

I'm not sure that the CAST() has the effect that you want. If you want the result as DECIMAL(18, 2), then cast after the SUM():

CAST(SUM(CASE WHEN t.TranType = 'SRT' THEN - t.Net
              WHEN t.TranType = 'DIV' THEN 0
              ELSE t.Net
          END) OVER () AS DECIMAL(18, 2)

You might want to cast before doing the sum, but for an accurate sum you should only cast afterwards.

user_11440350
Related