0

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,

1
  • 1
    Which RDBMS? Brackets suggest SQL Server?
    – Philᵀᴹ
    Nov 6, 2015 at 10:08

2 Answers 2

1

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

Added the sum function.

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.