Group by question

[aferrarese]

I have the following query results :

tel_num anom qta
8888 'A' 10
8888 'B' 15
6666 'S' 10

I should make a query on these data in order to get the results :

tel_num anom qta
8888 'X' 25
6666 'S' 10

That is, if for one tel_num there are more anom the result is 'X', if only one the result is the anom value.

Could you help me for the query?
Thanks

 

[sem]

select tel_num, decode(max(rowid), min(rowid), max(anom), 'X') 
from <table>
group by tel_num

Regards, Dima

http://seminihin.narod.ru

 

[jaggiebunnet]

I think what you are looking for is

select tel_num, anom, sum(qta)
from   your_table
group by tel_num, anom;

Although you are not too clear to be honest if this is what you are looking for.

 

[stefanhei]

Hi.
There might be a better solution, but this should work too:

SELECT a.tel_num,
       DEOCDE(cnt_anom,1,a.anom,'X'),
       SUM(qta)
  FROM your_table a, (SELECT tel_num,
                             COUNT(anom) cnt_anom
                        FROM your_table
                       GROUP BY tel_num) b
 WHERE a.tel_num=b.tel_num
 GROUP BY tel_num,DEOCDE(cnt_anom,1,a.anom,'X');

Stefan

 

[sem]

Oops, I see I've missed sum(qta):

select tel_num, decode(max(rowid), min(rowid), max(anom), 'X'), sum(qta)
from <table>
group by tel_num

Regards, Dima

http://seminihin.narod.ru

 

[aferrarese]

Thanks!
I think the best solution is the Sem one.
See you.