在 CASE 語句和 GROUP BY 子句中計算保證金

我想我在選擇如何在 group by 語句中聚合列時遇到了錯誤。

我正在計算 Margin % 並使用如下案例語句分組為幾個月：

SUM(CASE WHEN MONTH(DateProcessed) = 1 AND Revenus <> 0 THEN (Revenue - Cost) / Revenue ELSE 0 END AS [January]

所以我的等式最終為：SUM( (Revenue - Cost) / Revenue )

在幾個月內多次交易給了我錯誤的價值

我確信我的等式需要是：SUM(Revenue - Cost) / SUM(Revenue)

但我不能像那樣格式化我的案例陳述。你將如何計算出這種等式？

謝謝，

我沒有您的完整架構，但這應該適合您。

;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

添加了求和功能。

這是一種方法。

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

如果使用公用表表達式方法並且預計或包含一個月以上，則需要一個總和。

;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

引用自：https://dba.stackexchange.com/questions/120260