Analytics

turn on suggestions

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- 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

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

- 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

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

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

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.