Re: Using Single Query with Group by clause.

select ... /* customers with no orders */ union
select ... /* customers with 1 order */
union
select ... /* customers with >1 order */

"Srinivas" <skandu_at_yahoo.com> wrote in message news: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 :
> 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
>
> Test Case :
>
> create table ks_temp ( customer_id number , order_id number )
> insert into ks_temp values ( 10, 1000)
> insert into ks_temp values ( 20, 2000)
> insert into ks_temp values ( 30, 3000)
>
> Tried solutions :
> select count(*) openOrders, decode(count(*), 0,0,1,1,2, -999999)
> from ks_temp
> where CUSTOMER_ID = 20
>
> 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)
> 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 Fri Dec 20 2002 - 14:43:38 CET