help with sql 1

[Ravala]

I have
order_id status tr_number
123 null 99991
123 null 99992
123 V 99993
128 null 99994
128 V 99995
130 null 99996
130 null 99997
And I need to select orders which have at least one status "V" , and how many such orders:
Let say:
tr_number order_id count(with null) count(with 'V')
99993 123 2 1
99995 128 1 1

Thanks.

 

[sem]

select 
tr_number, 
order_id,  
SUM(decode(status,null,1,0)) "STATUS NULL", SUM(decode(status,'V',1,0)) "STATUS V"
from <TABLE>
group by tr_number, order_id

Regards, Dima

http://seminihin.narod.ru

 

[Ravala]

It gives me all orders, I need orders with at least one V status.
And it doesn't give me sum.

 

[SantaMufasa]

Ravala,

I'm surprised that Sem couldn't read your mind remotely from the Ukraine . Using Sem's excellent code, if you want only those orders having at least 1 'V', then try this code:

select 
tr_number, 
order_id,  
SUM(decode(status,null,1,0)) "STATUS NULL", SUM(decode(status,'V',1,0)) "STATUS V"
from <TABLE>
group by tr_number, order_id
having SUM(decode(status,'V',1,0))>= 1;

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 16:16 (21Oct04) UTC (aka "GMT" and "Zulu"),
@ 09:16 (21Oct04) Mountain Time

 

[Ravala]

Did you notice tr_number always different, it just breaks grouping. I have 0 in "STATUS NULL" and 1 in "STATUS V".

 

[Dagon]

Do you want to see all the TR_NUMBERs then or just a "representative" one. If the latter, just take the minimum:

select 
min(tr_number), 
order_id,  
SUM(decode(status,null,1,0)) "STATUS NULL", SUM(decode(status,'V',1,0)) "STATUS V"
from <TABLE>
group by order_id
having SUM(decode(status,'V',1,0))>= 1;