Link to home
Start Free TrialLog in
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<





Avatar of Sharper
Sharper

Try the following...

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

Just an untested thought.

Avatar of sparkplug

ASKER

Hi Sharper,

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

>S'Plug<
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.
when DATEPART(year, tblCAIR.DateRaised) is null then 'UNKNOWN'
else 'UNKNOWN'
end as YearRaised
Avatar of 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)
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<
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.
OK, so this is difficult one. Upping the points...

>S'Plug<
ASKER CERTIFIED SOLUTION
Avatar of Sharper
Sharper

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
with the tiny bit of test data I created anyway
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<
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.