By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,544 Members | 2,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,544 IT Pros & Developers. It's quick & easy.

Simple question on case statements and aggregating data

P: n/a
Hi - from my limited expereince in DB2, it appears that you can't
include a case statement in a group by clause - is this correct?

For example, my SQL looked like this:

SELECT CASE WHEN GROUP_CD<'00001' THEN 'ITS' ELSE 'NON' END AS TYPE,
COUNT(*) AS CNT FROM tblCLAIMS GROUP BY CASE WHEN GROUP_CD<'00001' THEN
'ITS' ELSE 'NON' END

My solution was to not initially group the data, and then summarize
the results in another query once the TYPE field had been populated.
Was my syntax incorrect or does DB2 not allow case statements in a
group by clause?

Thanks much!

Dave

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
recheck your code pls... does "SELECT CASE WHEN GROUP_CD<'00001' THEN
'ITS' ELSE 'NON' END AS TYPE FROM tblCLAIMS " work?

I make two tests on the sample database shipped with db2 udb and both
work fine.

select case when edlevel <18 then 'low' else 'high' end as edu,
count(*) from employee group by case when edlevel <18 then 'low' else
'high' end

or

with data as ( select case when edlevel <18 then 'low' else 'high' end
as edu from employee) select edu,count(*) from data group by edu

Nov 12 '05 #2

P: n/a
Could it be our version of DB2, or the fact that I'm running my query
as an Access Pass-through? The SQL I posted above works fine in our SQL
Server environment. It's no big deal to use a subquery first, but I was
hoping to gain some knowledge as to whether DB2 supports case
statements in group by clauses. Thanks for trying!

Nov 12 '05 #3

P: n/a
Fa******@gmail.com wrote:
Could it be our version of DB2, or the fact that I'm running my query
as an Access Pass-through? The SQL I posted above works fine in our SQL
Server environment. It's no big deal to use a subquery first, but I was
hoping to gain some knowledge as to whether DB2 supports case
statements in group by clauses. Thanks for trying!


Yes, DB2 LUW does support CASE expressions in GROUP BY clauses.

To understand what goes wrong in your system, you should provide the
information which DB2 you are using and what was the exact error message.
Also, have you tried your statement directly at the DB2 command line?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.