use of group by with union all

[sumitavasarkar]

i hv a code like this

select code, sum(amount) aaaa, 0 bbbb, 0 cccc
from f1
where .....

union all

select code, 0 aaaa, sum(amount) bbbb, 0 cccc
from f1
where .....

union all

select code, 0 aaaa, 0 bbbb, sum(amount) cccc
from f1
where .....

the select must be group by code.

whwre do i put the group by clause

[pradeep kaltari]

i hv a code like this

select code, sum(amount) aaaa, 0 bbbb, 0 cccc
from f1
where .....

union all

select code, 0 aaaa, sum(amount) bbbb, 0 cccc
from f1
where .....

union all

select code, 0 aaaa, 0 bbbb, sum(amount) cccc
from f1
where .....

the select must be group by code.

whwre do i put the group by clause

Hi,
You can put the GROUP BY clause after each of the SELECT statements. Something like:

 
select code, sum(amount) aaaa, 0 bbbb, 0 cccc
from f1
where .....
group by code
union all
 
select code, 0 aaaa, sum(amount) bbbb, 0 cccc
from f1
where .....
group by code
 

I hope this helps you.

Regards,
Pradeep