Sum Case when statement using Condition 1 minus Condition 2

Question

I have a table that contains product totals as well as a pre-calculated grand total

Type	Agent	Rev
Product 1	Agent 1	100
Product 2	Agent 1	90
Product 3	Agent 1	200
GrandTotal	Agent 1	390

I can SUM where Type = Grand Total or I could SUM where type is IN (Product 1, Product 2, Product 3)

However, I have a need to SUM GrandTotal minus Product 3. SUM where type is IN (Product 1, Product 2 ) won't work for my purposes

, SUM(CASE WHEN Type = 'GrandTotal'  THEN  ( Rev ) 
    - 
    WHEN Type = 'Product 3' THEN Rev
  ELSE 0 
  END
  ) as GT_ExcPrdct3

The answer I want to obtain is 190 -- 390-200

Answer 1

You need two separate conditional sums

SUM(CASE WHEN Type = 'GrandTotal' THEN Rev END)
- 
SUM(CASE WHEN Type = 'Product 3' THEN Rev END)

I agree with others that it would be better to not to store the grand total and instead calculate it when needed.

You can use ROLLUP or GROUPING SETS for this. Exactly what should happen to the Agent column is not clear.

SELECT
  CASE WHEN GROUPING(t.Type) = 1 THEN 'Grand Total' ELSE t.Type END AS Type,
  t.Agent,
  SUM(CASE WHEN Type <> 'Product 3' THEN Rev END) AS Rev
FROM YourTable t
GROUP BY GROUPING SETS (
    (t.Type, t.Agent),
    ()
);