SQL Server - group by 中的用例语句

Question

我试图创建一个视图，如下所示：

CREATE VIEW vw1 AS SELECT     
  Town
, case when (Spend > 0 and NbOrders > 0)
       then sum(Spend)/sum(NbOrders)
       else null
       end as AvgSpend
, case when (Margin > 0 and NbOrders > 0)
       then sum(Margin)/sum(NbOrders)
       else null
       end as AvgMargin
FROM Table
group by Town

但是我收到错误，因为“case when...”语句中使用的字段不包含在聚合函数中。

我无法使用“where”子句，因为平均字段取决于大于 0 的不同字段，并且我不知道在聚合字段上工作时如何证明这一点？

我有什么想法可以实现这一点 - 最好是在一个声明中？

Answer 1

CREATE VIEW vw1 AS
SELECT
  Town
, SUM(CASE WHEN Spend > 0 THEN Spend END)
  / SUM(CASE WHEN NbOrders > 0 THEN NbOrders END) AS AvgSpend
, SUM(CASE WHEN Margin > 0 THEN Margin END)
  / SUM(CASE WHEN NbOrders > 0 THEN NbOrders END) AS AvgMargin
FROM Table
GROUP BY Town

Answer 2

更新了...感谢@Andriy M

SELECT town

,SUM(CASE WHEN Spend > 0 and NbOrders > 0 THEN Spend END)
/SUM(CASE WHEN Spend > 0 and NbOrders > 0 THEN NbOrders END)  As AvgSpend

,SUM(CASE WHEN Margin > 0 and NbOrders > 0 THEN Margin END)
/ SUM(CASE WHEN Margin > 0 and NbOrders > 0 THEN NbOrders END) As AvgMargin

FROM table
GROUP BY town

Answer 3

您只需要在每个“案例”中包含 group by 即可。

像下面这样：

CREATE VIEW vw1 AS SELECT     
  Town
, case when (Spend > 0 and NbOrders > 0)
       then sum(Spend)/sum(NbOrders)
       group by Town
       else null
       end as AvgSpend
, case when (Margin > 0 and NbOrders > 0)
       then sum(Spend)/sum(NbOrders)
       group by Town
       else null
       end as AvgMargin
FROM Table

Answer 4

CREATE VIEW vw1 AS SELECT     
  Town
, case when (Spend > 0 and NbOrders > 0)
       then sum(Spend)/sum(NbOrders)
       else null
       end as AvgSpend
, case when (Margin > 0 and NbOrders > 0)
       then sum(Spend)/sum(NbOrders)
       else null
       end as AvgMargin
FROM Table
group by Town, AvgSpend, AvgMargin