Archives of the TeradataForum
Message Posted: Thu, 13 Nov 2008 @ 18:13:59 GMT
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.
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;
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|