troubleshooting Question

ROLL UP query with DATEPART grouping

Avatar of sparkplug
sparkplug asked on
Microsoft SQL Server
12 Comments1 Solution561 ViewsLast Modified:
Hi,

I have the following query for grouping records by Issuer and Year.

SELECT
     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

FROM tblCAIR
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,

>S'Plug<





ASKER CERTIFIED SOLUTION
Sharper

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros