Tosh - 6 months ago 45

SQL Question

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