Group by case

[mjuell]

How do i group by a case in oracle. ?
In sybase my statement looks like this.

select case when c.cust_unit_number is null then 'private' else 'business' end,count(distinct a.cust_id_resp)
from cm.account a, cm.customer c
where a.info_is_deleted = 'N'
and a.foreign_account is not null
and a.cust_id_resp = c.cust_id
and c.info_is_deleted = 'N'
group by case when c.cust_unit_number is null then 'privat' else 'bedrift' end

 

[SantaMufasa]

MJuell,

I just successfully ran a query that is syntactically similar to yours:

select case when salary > 1000 then 'Hi Pay' else 'Lo Pay' end Grade, count(*) HowMany
from s_emp
group by case when salary > 1000 then 'Hi Pay' else 'Lo Pay' end
/

GRADE     HOWMANY
------ ----------
Hi Pay         18
Lo Pay          7

2 rows selected.

Does this resolve your problem?

Mufasa
(aka Dave of Sandy, Utah, USA @ 11:07 (06May04) UTC (aka "GMT" and "Zulu"), 04:07 (06May04) Mountain Time)

 

[mjuell]

thanx. it works.

 

[parbhani]

Hi ,

You guys have a small CASE, so things look good.

I have few queries which use very big CASE, around 9 to 10 conditions, but not a group by. If at all I have to use group by in such cases, the query will look like a mess.

Any suggessions ?

Cheers

 

[sem]

Try to use subquery and aliases:

select a, sum(b) from 
  (select <really big case> a, b from tables)
group by a

Regards, Dima

http://seminihin.narod.ru