problem with Group By

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

re: problem with Group By

<sangram.0149@gmail.com> wrote in message
news:1142574711.398622.37520@e56g2000cwe.googlegro ups.com...[color=blue]
> 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
>[/color]

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.

re: problem with Group By

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.


sangram.0149@gmail.com wrote:[color=blue]
> 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[/color]

re: problem with Group By

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

re: problem with Group By

In article <1142589042.294553.226980@z34g2000cwc.googlegroups .com>,
sangram.0149@gmail.com says...[color=blue]
> 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
>
>[/color]

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

re: problem with Group By

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