The ROLLUP, CUBE, and GROUPING SETS operators are extensions of the GROUP BY clause. The GROUPING SET is the union of the aggregates based on the columns specified in each set in the grouping set. Multiple columns that are in inner parentheses in the GROUPING SETS list are treated as a single set. The following query will display each region and department combination with their total sales and each region with its total sales.

SELECT Region, Department, sum(sales) Total_Sales
FROM TBL_EMPLOYEE
GROUP BY GROUPING SETS ((Region, Department), (Region))

REGION     DEPARTMENT TOTAL_SALES
---------- ---------- -----------
NORTH      CARS               135
NORTH      BOATS               76
NORTH      AIRPLANES           25
NORTH                         236
SOUTH      CARS               154 
SOUTH      BOATS               31 
SOUTH      AIRPLANES           12
SOUTH                         197

Specifying GROUPING SETS as the GROUP BY list is the same as a UNION ALL of queries, each with one of the grouping sets as its GROUP BY list. It is usually more efficient. For example, the following queries are equivalent:

SELECT customer_name, year, SUM(sales)
FROM TBL_CUSTOMERS
GROUP BY GROUPING SETS ((customer_name), (year))

SELECT customer_name, NULL as year, SUM(sales)
FROM TBL_CUSTOMERS
GROUP BY customer_name
UNION ALL
SELECT NULL as customer_name, year, SUM(sales)
FROM TBL_CUSTOMERS
GROUP BY year

ROLLUP enables one to calculate multiple levels of subtotals across a specified group of. It produces group subtotals from right to left and a grand total. ROLLUP is very efficient and adds minimal overhead to a query. If “n” is the number of columns in the ROLLUP, then n+1 is the number of levels of subtotals. The following query will display the sales total for each customer name and year combination, the sales total for each customer name, and a grand total of all sales.

SELECT customer_name, year, SUM(sales) AS total_sales
FROM TBL_CUSTOMERS
GROUP BY ROLLUP (customer_name, year)
ORDER BY (customer_name, year)

CUSTOMER_NAME YEAR   TOTAL_SALES
------------- -----  -----------
ABC Company    2005          100
ABC Company    2006          200
ABC Company    2007          350
ABC Company                  650
XYZ Company    2005          180
XYZ Company    2006          225
XYZ Company    2007          275
XYZ Company                  680
                            1330

CUBE creates subtotals for all possible combinations of the columns listed in addition to a grand total. The following query will display the sales total for each customer name and year combination, the sales total for each customer name, the sales total for each year, and a grand total of all sales.

SELECT customer_name, year, SUM(sales) AS total_sales
FROM TBL_CUSTOMERS
GROUP BY CUBE (customer_name, year)
ORDER BY (customer_name, year)

CUSTOMER_NAME YEAR   TOTAL_SALES
------------- -----  -----------
ABC Company    2005          100
ABC Company    2006          200
ABC Company    2007          350
ABC Company                  650
XYZ Company    2005          180
XYZ Company    2006          225
XYZ Company    2007          275
XYZ Company                  680
               2005          280
               2006          425
               2007          625
                            1330