sqlcasesubtraction

Sum Case when statement using Condition 1 minus Condition 2


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


Solution

  • 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),
        ()
    );