CASE语句中计算月度Margin遇聚合函数错误的技术问询

The core issue with your current approach is that your variables @Sales, @Cost, and @Margin are calculating totals across all months in the table, not per month. That’s why you’re getting incorrect results, and why you might have run into the aggregate-on-aggregate error if you tried nesting calculations earlier.

To compute monthly margins correctly, you need to calculate sales and cost totals per month directly in your SELECT statement, just like you did for monthly sales. Here’s how to adjust your query:

SELECT
    -- January Margin (handle division by zero to avoid errors)
    (SUM(CASE WHEN CalendarMonth = 1 THEN TotalSales ELSE 0 END) - SUM(CASE WHEN CalendarMonth = 1 THEN TotalCost ELSE 0 END))
    / NULLIF(SUM(CASE WHEN CalendarMonth = 1 THEN TotalSales ELSE 0 END), 0) AS January,
    
    -- February Margin
    (SUM(CASE WHEN CalendarMonth = 2 THEN TotalSales ELSE 0 END) - SUM(CASE WHEN CalendarMonth = 2 THEN TotalCost ELSE 0 END))
    / NULLIF(SUM(CASE WHEN CalendarMonth = 2 THEN TotalSales ELSE 0 END), 0) AS February,
    
    -- March Margin
    (SUM(CASE WHEN CalendarMonth = 3 THEN TotalSales ELSE 0 END) - SUM(CASE WHEN CalendarMonth = 3 THEN TotalCost ELSE 0 END))
    / NULLIF(SUM(CASE WHEN CalendarMonth = 3 THEN TotalSales ELSE 0 END), 0) AS March,
    
    -- April Margin
    (SUM(CASE WHEN CalendarMonth = 4 THEN TotalSales ELSE 0 END) - SUM(CASE WHEN CalendarMonth = 4 THEN TotalCost ELSE 0 END))
    / NULLIF(SUM(CASE WHEN CalendarMonth = 4 THEN TotalSales ELSE 0 END), 0) AS April,
    
    -- May Margin
    (SUM(CASE WHEN CalendarMonth = 5 THEN TotalSales ELSE 0 END) - SUM(CASE WHEN CalendarMonth = 5 THEN TotalCost ELSE 0 END))
    / NULLIF(SUM(CASE WHEN CalendarMonth = 5 THEN TotalSales ELSE 0 END), 0) AS May,
    
    -- June Margin
    (SUM(CASE WHEN CalendarMonth = 6 THEN TotalSales ELSE 0 END) - SUM(CASE WHEN CalendarMonth = 6 THEN TotalCost ELSE 0 END))
    / NULLIF(SUM(CASE WHEN CalendarMonth = 6 THEN TotalSales ELSE 0 END), 0) AS June,
    
    -- July Margin
    (SUM(CASE WHEN CalendarMonth = 7 THEN TotalSales ELSE 0 END) - SUM(CASE WHEN CalendarMonth = 7 THEN TotalCost ELSE 0 END))
    / NULLIF(SUM(CASE WHEN CalendarMonth = 7 THEN TotalSales ELSE 0 END), 0) AS July,
    
    -- August Margin
    (SUM(CASE WHEN CalendarMonth = 8 THEN TotalSales ELSE 0 END) - SUM(CASE WHEN CalendarMonth = 8 THEN TotalCost ELSE 0 END))
    / NULLIF(SUM(CASE WHEN CalendarMonth = 8 THEN TotalSales ELSE 0 END), 0) AS August,
    
    -- September Margin
    (SUM(CASE WHEN CalendarMonth = 9 THEN TotalSales ELSE 0 END) - SUM(CASE WHEN CalendarMonth = 9 THEN TotalCost ELSE 0 END))
    / NULLIF(SUM(CASE WHEN CalendarMonth = 9 THEN TotalSales ELSE 0 END), 0) AS September,
    
    -- October Margin
    (SUM(CASE WHEN CalendarMonth = 10 THEN TotalSales ELSE 0 END) - SUM(CASE WHEN CalendarMonth = 10 THEN TotalCost ELSE 0 END))
    / NULLIF(SUM(CASE WHEN CalendarMonth = 10 THEN TotalSales ELSE 0 END), 0) AS October,
    
    -- November Margin
    (SUM(CASE WHEN CalendarMonth = 11 THEN TotalSales ELSE 0 END) - SUM(CASE WHEN CalendarMonth = 11 THEN TotalCost ELSE 0 END))
    / NULLIF(SUM(CASE WHEN CalendarMonth = 11 THEN TotalSales ELSE 0 END), 0) AS November,
    
    -- December Margin
    (SUM(CASE WHEN CalendarMonth = 12 THEN TotalSales ELSE 0 END) - SUM(CASE WHEN CalendarMonth = 12 THEN TotalCost ELSE 0 END))
    / NULLIF(SUM(CASE WHEN CalendarMonth = 12 THEN TotalSales ELSE 0 END), 0) AS December
FROM SalesAnalysis2;

Key Improvements: #

1. Per-Month Aggregates: Each month’s margin uses sales and cost totals specific to that month (not the entire table).
2. Division by Zero Protection: NULLIF ensures you don’t get a division error if sales for a month are zero (it returns NULL instead; if you prefer 0 instead of NULL, use a CASE statement to handle that).
3. Single Row Result: This query returns one row with all 12 months’ margins, which is likely what you want (no need for DISTINCT here).

If you want to format the margin as a percentage (e.g., 25 instead of 0.25), multiply the result by 100:

ROUND(
    ((SUM(CASE WHEN CalendarMonth = 1 THEN TotalSales ELSE 0 END) - SUM(CASE WHEN CalendarMonth = 1 THEN TotalCost ELSE 0 END))
    / NULLIF(SUM(CASE WHEN CalendarMonth = 1 THEN TotalSales ELSE 0 END), 0)) * 100,
    2
) AS January_Percent

内容的提问来源于stack exchange，提问作者M.tovey