All Forums Analytics
adash-7422 33 posts Joined 01/09
03 Feb 2010
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 - 1400Level 2 - 1600Total - 3000if 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?

ThisIsBob 6 posts Joined 09/06
03 Feb 2010

Would a union work?select level_code,sum(sum_field) from level_groups group by level_codeunionselect 'Total',sum(sum_field) from level_groupsorder by level_code

dnoeth 4628 posts Joined 11/04
03 Feb 2010

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_codeDieter


You must sign in to leave a comment.