ROLL UP query with DATEPART grouping
Posted on 2003-03-03
I have the following query for grouping records by Issuer and Year.
CASE WHEN (GROUPING(tblCAIR.Issuer) = 1) THEN '(ALL)'
WHEN tblCAIR.Issuer='' THEN 'UNKNOWN'
ELSE ISNULL(tblCAIR.Issuer, 'UNKNOWN')
END AS Issuer,
CASE WHEN (GROUPING(DATEPART(year, tblCAIR.DateRaised)) = 1) THEN '(ALL)'
WHEN DATEPART(year, tblCAIR.DateRaised)='' THEN 'UNKNOWN'
ELSE ISNULL(tblCAIR.DateRaised, 'UNKNOWN')
END AS YearRaised,
COUNT(*) AS NumberOfRecords
GROUP BY tblCAIR.Issuer, DATEPART(year, tblCAIR.DateRaised) WITH ROLLUP
ORDER BY tblCAIR.Issuer, DATEPART(year, tblCAIR.DateRaised)
This should produce a roll-up table of record counts. It results, however, in the following error:
"Column 'tblCAIR.DateRaised' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.".
This error is generated by the line "ELSE ISNULL(tblCAIR.DateRaised, 'UNKNOWN')" which is there to deal with NULL values in the resultset. If I remove this line the error goes but as expected I get a list of NULLs in the YearRaised column.
Does anyone know how to fix this query?
The intended output is similar to the following:
Issuer YearRaised NumberOfRecords
(ALL) (ALL) 12
Some Name (ALL) 6
Some Name 2002 2
Some Name 2003 2
Some Name UNKNOWN 1
Other Name (ALL) 4
Other Name 2002 1
Other Name 2003 1
Other Name UNKNOWN 2
UNKNOWN UNKNOWN 2
Issuer is nvarchar(100)
DateRaised is smalldatetime
I'm using SQL Server 7 SP3
Thanks in advance,