Mastering Oracle SQL

The GROUPING Function

ROLLUP and CUBE produce extra rows in the output that contain subtotals and totals. These rows contain NULL values for one or more columns. An output containing NULLs and indicating subtotals doesn’t make sense to an ordinary person who is unware of the behavior of ROLLUP and CUBE operations. Does your VP care about whether you used ROLLUP or CUBE or any other operation to get him the monthly total sales for each region? Obviously, he doesn’t. That’s exactly why you are reading this page and not your VP.

If you know your way around the NVL function, you would probably attempt to translate each NULL value from CUBE and ROLLUP to some descriptive value, as in the following example:

            SELECT NVL(TO_CHAR(O.YEAR), 'All Years') YEAR,
            NVL(TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month'), 'First Quarter') MONTH,
            NVL(R.NAME, 'All Regions') REGION, SUM(O.TOT_SALES)
            FROM ORDERS O, REGION R
            WHERE R.REGION_ID = O.REGION_ID
            AND O.MONTH BETWEEN 1 AND 3
            GROUP BY ROLLUP (O.YEAR, O.MONTH, R.NAME); YEAR MONTH REGION SUM(O.TOT_SALES) ---------------- ------------- -------------------- ---------------- 2000 January Mid-Atlantic 1221394 2000 January New England 1018430 2000 January SouthEast US 758042 2000 January All Regions 2997866 2000 February Mid-Atlantic 857352 2000 February New England 1231492 2000 February SouthEast US 1236846 2000 February All Regions 3325690 2000 March Mid-Atlantic 1274062 2000 March New England 1132966 2000 March SouthEast US 1311986 2000 March All Regions 3719014 ...