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