Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

Message Posted: Thu, 13 Nov 2008 @ 18:13:59 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Option for getting subtotals in a data column
 
From:   Curley, David

You can also group by rollup (or cube if you need every possible subtotal):

     Select grp1, grp2, sum(num1)
      from tbl1
     Group by rollup (grp1, grp2)
     Order by grouping(grp1), grp1, grouping(grp2), grp2

The subtotal values will be NULL, so you can coalesce them to an appropriate text string if you want. There will also be a grand total with grp1 and grp2 as NULL.

The function grouping(field) returns 0 if field is not being totaled on that row and 1 if it is. You can add it to the select list to better see what's going on.

Example:

     select day_of_week, week_of_month, sum(day_of_calendar),  grouping
     (day_of_week),  grouping(week_of_month)
     from (select top 20 day_of_week, week_of_month, day_of_calendar
     from sys_calendar.calendar) c
     group by rollup (1,2)
     order by grouping (day_of_week), day_of_week,  grouping(week_of_month),
     week_of_month;

Dave



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020