Analytics

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-03-2010
12:40 PM

02-03-2010
12:40 PM

Getting the sum without using the columns in group by

Hi,

We have a requirement where we need to show the total sum as well as sum grouped based on levels :

eg: If there are 3000 orders , 1400 is under level 1 and 1600 under level 2, then data should be like:

Level 1 - 1400

Level 2 - 1600

Total - 3000

if we use something like select sum(order_count),level from group by level then i get the level counts, what about the overall count? How do we achieve that?

We have a requirement where we need to show the total sum as well as sum grouped based on levels :

eg: If there are 3000 orders , 1400 is under level 1 and 1600 under level 2, then data should be like:

Level 1 - 1400

Level 2 - 1600

Total - 3000

if we use something like select sum(order_count),level from group by level then i get the level counts, what about the overall count? How do we achieve that?

2 REPLIES 2

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-03-2010
03:35 PM

02-03-2010
03:35 PM

Re: Getting the sum without using the columns in group by

Would a union work?

select level_code,sum(sum_field) from level_groups group by level_code

union

select 'Total',sum(sum_field) from level_groups

order by level_code

select level_code,sum(sum_field) from level_groups group by level_code

union

select 'Total',sum(sum_field) from level_groups

order by level_code

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-03-2010
03:58 PM

02-03-2010
03:58 PM

Re: Getting the sum without using the columns in group by

This is exactly what those GROUP BY-extendens are ment for:

SELECT

CASE

WHEN GROUPING (level_code) = 1 THEN 'Total'

ELSE level_code

END,

SUM(sum_field) FROM level_groups

GROUP BY GROUPING SETS ((level_code),())

ORDER BY GROUPING(level_code), level_code

Dieter

SELECT

CASE

WHEN GROUPING (level_code) = 1 THEN 'Total'

ELSE level_code

END,

SUM(sum_field) FROM level_groups

GROUP BY GROUPING SETS ((level_code),())

ORDER BY GROUPING(level_code), level_code

Dieter