Using Single Query with Group by clause.
Date: 19 Dec 2002 12:00:52 -0800
Message-ID: <c6a7eae9.0212191200.5f2ba20a_at_posting.google.com>
Hi,
I have this requirement which I need to achieve using a Single SQL
Query ( using oracle db ).
Scenario :
Let's say we have the a table ks_temp which has order_id and
customer_id. A customer can have 0 orders,
1 order or more orders.
My requirement :
Test Case :
create table ks_temp ( customer_id number , order_id number )
Tried solutions :
This works fine but does not give order_id. The moment I change it to
select count(*) openOrders, decode(count(*), 0,0,1,order_id,2,
-999999)
In a single Query, I want to get the COUNT of orders AND
If the count is 1, I want to get the order_id .
If the count is 0, I want the value 0,
If the count is >1 , I want some dummy value say 999999
insert into ks_temp values ( 10, 1000)
insert into ks_temp values ( 20, 2000)
insert into ks_temp values ( 30, 3000)
select count(*) openOrders, decode(count(*), 0,0,1,1,2, -999999)
from ks_temp
where CUSTOMER_ID = 20
from ks_temp
where CUSTOMER_ID = 20
I get an error. Can anyone suggest how to achieve this ??
thanks in advance for the response. Received on Thu Dec 19 2002 - 21:00:52 CET