By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,333 Members | 1,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,333 IT Pros & Developers. It's quick & easy.

problem with Group By

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/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

P: n/a
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

P: n/a
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

P: n/a
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

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

Mar 18 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.