Calculating Margin in CASE Statement and GROUP BY Clause I think I am running into an error in how I have chosen to aggregate my columns in a group by statement.

I am working out Margin % and grouping into months using a case statement like so:

SUM(CASE WHEN MONTH(DateProcessed) = 1 AND Revenus &lt;&gt; 0 THEN (Revenue - Cost) / Revenue ELSE 0 END AS [January]


So my equation ends up as: SUM( (Revenue - Cost) / Revenue )

Which in months with multiple transactions gives me the wrong value

I am sure my equation needs to be: SUM(Revenue - Cost) / SUM(Revenue)

But I cannot format my case statement like that. How would you work out this sort  of equation?

Thanks,
Here's one way.

select
 (select (sum(Revenue) - sum(Cost)) / sum(Revenue) from tmp where month(DateProcessed) = 1) as jan
,(select (sum(Revenue) - sum(Cost)) / sum(Revenue) from tmp where month(DateProcessed) = 2) as feb
,(select (sum(Revenue) - sum(Cost)) / sum(Revenue) from tmp where month(DateProcessed) = 3) as mar


If using the common table expression approach and more than a month is expected or included then a sum will be needed.

;with CTE_summary as (
    select
         month(DateProcessed) as monthProcessed
        ,sum(isnull(Revenue, 0.0)) as Revenuesum
        ,sum(isnull(Cost, 0.0)) as Costsum
    from tmp
    where year(dateprocessed) = 2015
    group by month(DateProcessed)
)
select
     sum(case when monthProcessed = 1 then (Revenuesum - Costsum) / Revenuesum else 0.0 end) as [jan]
    ,sum(case when monthProcessed = 2 then (Revenuesum - Costsum) / Revenuesum else 0.0 end) as [feb]
    ,sum(case when monthProcessed = 3 then (Revenuesum - Costsum) / Revenuesum else 0.0 end) as [mar]
    --,...
from CTE_summary