Avatar of sparkplug
sparkplug
 asked on

ROLL UP query with DATEPART grouping

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<





Microsoft SQL Server

Avatar of undefined
Last Comment
Sharper

8/22/2022 - Mon
Sharper

Try the following...

WHEN DATEPART(year, ISNULL(tblCAIR.DateRaised, dateadd('yy', 100, getdate() )))=dateadd('yy', 100, getdate() ) THEN 'UNKNOWN'

Just an untested thought.

sparkplug

ASKER
Hi Sharper,

Didn't make any difference. I'm kind of confused about where this thought was going though?

>S'Plug<
Sharper

I was thinking that the previous case works, but the else doesn't.  The case uses datepart and the else dosn't, so I was trying to get the datepart into the else to see if it made a difference.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Sharper

when DATEPART(year, tblCAIR.DateRaised) is null then 'UNKNOWN'
else 'UNKNOWN'
end as YearRaised
Scott Pletcher

You should be able to GROUP BY the entire expression, for example:

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  
    CASE WHEN (GROUPING(tblCAIR.Issuer) = 1) THEN '(ALL)'
    WHEN tblCAIR.Issuer='' THEN 'UNKNOWN'
    ELSE ISNULL(tblCAIR.Issuer, 'UNKNOWN')
    END,    
    CASE WHEN (GROUPING(DATEPART(year, tblCAIR.DateRaised)) = 1) THEN '(ALL)'
    WHEN DATEPART(year, tblCAIR.DateRaised)='' THEN 'UNKNOWN'
    ELSE ISNULL(tblCAIR.DateRaised, 'UNKNOWN')
    END    WITH ROLLUP
ORDER BY tblCAIR.Issuer, DATEPART(year, tblCAIR.DateRaised)
sparkplug

ASKER
Nice try Scott. That looked like it would work, however I get this error message: "Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause." I guess the CASE statement is classed as a subquery.

Sharper,

What I am looking for is more like:

when DATEPART(year, tblCAIR.DateRaised) is null then 'UNKNOWN'
else tblCAIR.DateRaised

which is what the ISNULL function does. This however does not work either. The problem is something to do with using the GROUP BY clause.

>S'Plug<
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Scott Pletcher

The GROUPING is the problem; I should have realized that earlier.  I'm not sure how to fix it within SQL's rules.  I'll keep thinking about it, though.
sparkplug

ASKER
OK, so this is difficult one. Upping the points...

>S'Plug<
ASKER CERTIFIED SOLUTION
Sharper

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Sharper

with the tiny bit of test data I created anyway
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
sparkplug

ASKER
Cool!

That does the job. Although I can't figure out exactly why. I guess it needed an aggregate on an int in their somewhere.

I've managed to compress it slightly:

CASE WHEN (GROUPING(DATEPART(year, tblCAIR.DateRaised)) = 1) THEN '(ALL)'
     WHEN DATEPART(year, tblCAIR.DateRaised)='' THEN 'UNKNOWN'
     ELSE ISNULL(cast(SUM(datepart(year, tblCAIR.DateRaised))/count(*) as varchar), 'UNKNOWN')
     END AS YearRaised,

This seems to work just the same.

Some well-deserved points go to Sharper.

Thanks to all for the help,


>S'Plug<
Sharper

Thanks,

Basically, It was aggregating on the year.  Since I knew the number or record in the year.  I summed the datevalue and then divided by the count to return the original value.  Like I said, "ugly, but it works."

You mod actually makes it work more smoothly since all you want is the year.  If you want the full date, I think you would have to use my ugly mess.