problem with Group By

sangram.0149

hi !
could someone plz help me on this one,its kinda urgent
i hav a result set which looks something like this
country_code sum(amount)

1 100

2 123

3 213
i hav already used 'group by' clause to get the sum of amounts for
each country code
now i need to find the total sum of sum(amount) in the same query.How
do i do it???

i hav tried using an alias for the aggregated column and then trying to
get the sum of that column
but it does not work

plz help me out

regards,
Sangram

Mar 17 '06 #1

Mark A

<sa**********@gmail.com> wrote in message
news:11*********************@e56g2000cwe.googlegro ups.com...

hi !
could someone plz help me on this one,its kinda urgent
i hav a result set which looks something like this
country_code sum(amount)

1 100

2 123

3 213
i hav already used 'group by' clause to get the sum of amounts for
each country code
now i need to find the total sum of sum(amount) in the same query.How
do i do it???

i hav tried using an alias for the aggregated column and then trying to
get the sum of that column
but it does not work

plz help me out

regards,
Sangram

select workdept, sum(salary) as salary from emp group by workdept
union
select '-TOTAL' as workdept, sum(salary) as salary from emp

Note that the "-" in front of 'TOTAL" ensures that the TOTAL line will sort
after the other departments.

Mar 17 '06 #2

Hardy

select country_code, sum(amount) as sum from tablename group by
rollup(dept)
select country_code, sum(amount) as sum from tablename group by
cube(dept)

here, rollup and cube is the same.

if you don't want the '-' in the result, try 'coalesce(country_code,4)'
or some others.
sa**********@gmail.com wrote:

hi !
could someone plz help me on this one,its kinda urgent
i hav a result set which looks something like this
country_code sum(amount)

1 100

2 123

3 213
i hav already used 'group by' clause to get the sum of amounts for
each country code
now i need to find the total sum of sum(amount) in the same query.How
do i do it???

i hav tried using an alias for the aggregated column and then trying to
get the sum of that column
but it does not work

plz help me out

regards,
Sangram

Mar 17 '06 #3

sangram.0149

I MIGHT HAV NOT STATED MY PROB CLEARLY.
IN MY RESULT SET I JUST WANT THE TOTAL AMOUNT ie. THE SUM OF ALL SUMS.I
DO NOT WANT THE RESULT SET THAT I HAV SHOWN ABOVE.
SO PLZ HELP ME
REGARDS ,
SANGRAM

Mar 17 '06 #4

Gert van der Kooij

In article <11**********************@z34g2000cwc.googlegroups .com>,
sa**********@gmail.com says...

I MIGHT HAV NOT STATED MY PROB CLEARLY.
IN MY RESULT SET I JUST WANT THE TOTAL AMOUNT ie. THE SUM OF ALL SUMS.I
DO NOT WANT THE RESULT SET THAT I HAV SHOWN ABOVE.
SO PLZ HELP ME
REGARDS ,
SANGRAM

If you just want to sum all amounts then
something like 'select sum(amount) from table' is enouhg, no need to
grouop by.

Mar 17 '06 #5

--CELKO--

1) Drop the GROUP BY and just SUM() them.
2) Start using ISO country codes instead of integers.

Mar 18 '06 #6