Getting the sum without using the columns in group by

Analytics
Enthusiast

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?
2 REPLIES
Enthusiast

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
Junior Contributor

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