?
Solved

ROLL UP query with DATEPART grouping

Posted on 2003-03-03
12
Medium Priority
?
442 Views
Last Modified: 2012-05-05
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<





0
Comment
Question by:sparkplug
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
12 Comments
 
LVL 2

Expert Comment

by:Sharper
ID: 8058888
Try the following...

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

Just an untested thought.

0
 
LVL 9

Author Comment

by:sparkplug
ID: 8059065
Hi Sharper,

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

>S'Plug<
0
 
LVL 2

Expert Comment

by:Sharper
ID: 8059135
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.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 2

Expert Comment

by:Sharper
ID: 8059189
when DATEPART(year, tblCAIR.DateRaised) is null then 'UNKNOWN'
else 'UNKNOWN'
end as YearRaised
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8059240
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)
0
 
LVL 9

Author Comment

by:sparkplug
ID: 8059959
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<
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8060029
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.
0
 
LVL 9

Author Comment

by:sparkplug
ID: 8062827
OK, so this is difficult one. Upping the points...

>S'Plug<
0
 
LVL 2

Accepted Solution

by:
Sharper earned 1200 total points
ID: 8065540
This is ugly, but it works.

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(cast(datepart(year,cast(SUM(cast(tblCAIR.DateRaised as int))/count(*) as datetime))as varchar), '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)
0
 
LVL 2

Expert Comment

by:Sharper
ID: 8065548
with the tiny bit of test data I created anyway
0
 
LVL 9

Author Comment

by:sparkplug
ID: 8065866
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<
0
 
LVL 2

Expert Comment

by:Sharper
ID: 8065914
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.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question