# 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 <> 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,

• Which RDBMS? Brackets suggest SQL Server? Nov 6, 2015 at 10:08

I don't have your full schema, but this should work for you.

;WITH CTE_Summary AS
(
SELECT MONTH(DateProcessed) AS MonthProcessed
, SUM(ISNULL(Revenue, 0.0)) AS RevenueSum
, SUM(ISNULL(Cost, 0.0)) AS CostSum
FROM <TABLE NAME>
GROUP BY MONTH(DateProcessed)
)
SELECT sum(CASE WHEN MonthProcessed = 1 THEN (RevenueSum - CostSum) / RevenueSum ELSE 0.0 END) AS [January]
FROM CTE_Summary


• Thank you, this solves my issue. It was staring me right in the face! Nov 9, 2015 at 0:00

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