Tosh Tosh - 1 month ago 12
SQL Question

Excluding Condition From SUM Function SQL Server 2014

I am fairly new to SQL and I can not seem to figure a way to produce the results I am looking for. I am trying to get a SUM of all the A/R buckets by aging bucket and by facility. Within the buckets are a few different categories (Insurance, Lien & Pt. Responsibility) I want to include all categories in the SUM except "Lien". I keep getting the GROUP BY aggregate error. Can someone please assist? Thanks!

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

You should simply exclude those rows from your query with a where clause.

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]
Comments