从SUM函数排除条件SQL Server 2014

Question

我对SQL相当陌生，我似乎无法找到一种方法来产生我正在寻找的结果。我正试图通过老化桶和设施获得所有a/R桶的总和。在这些桶里有几个不同的类别(保险、留置权;(责任)我想在SUM中包括除"留置权"以外的所有类别。我一直得到GROUP BY聚合错误。有人能帮忙吗?谢谢!

SELECT ar.[Report Date]
,ar.Facility
,(CASE WHEN ar.[Current Financial Class] <> 'Lien' THEN SUM (ar.[0-30]) END) AS [0-30]
,(CASE WHEN ar.[Current Financial Class] <> 'Lien' THEN SUM (ar.[31-60]) END) AS [31-60]
,(CASE WHEN ar.[Current Financial Class] <> 'Lien' THEN SUM (ar.[61-90]) END) AS [61-90]
,(CASE WHEN ar.[Current Financial Class] <> 'Lien' THEN SUM (ar.[91-120] + ar.[120+]) END) AS [91+]
FROM DBO.ARByPayer AS ar
GROUP BY ar.Facility, ar.[Report Date]

Answer 1

您应该使用where子句简单地从查询中排除这些行。

SELECT ar.[Report Date]
    , ar.Facility
    , SUM (ar.[0-30]) AS [0-30]
    , SUM (ar.[31-60]) AS [31-60]
    , SUM (ar.[61-90]) AS [61-90]
    , SUM (ar.[91-120] + ar.[120+]) AS [91+]
FROM DBO.ARByPayer AS ar
WHERE ar.[Current Financial Class] <> 'Lien'
GROUP BY ar.Facility
    , ar.[Report Date]

Answer 2

可以通过子查询实现。

Select [Report Date], Facility,  sum([0-30]) AS [0-30], sum([31-60]) AS [31-60], sum([61-90]) AS [61-90], sum([91+]) AS [91+]
        (SELECT ar.[Report Date]
        ,ar.Facility
        ,(CASE WHEN ar.[Current Financial Class] <> 'Lien' THEN ar.[0-30] END) AS [0-30]
        ,(CASE WHEN ar.[Current Financial Class] <> 'Lien' THEN ar.[31-60] END) AS [31-60]
        ,(CASE WHEN ar.[Current Financial Class] <> 'Lien' THEN ar.[61-90] END) AS [61-90]
        ,(CASE WHEN ar.[Current Financial Class] <> 'Lien' THEN ar.[91-120] + ar.[120+]) END) AS [91+]
        FROM DBO.ARByPayer AS ar) t
        GROUP BY Facility, [Report Date]