Archives of the TeradataForum

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

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

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