Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS

The ROLLUP, CUBE, and GROUPING SETS operators are extensions of the GROUP BY clause. The ROLLUP, CUBE, or GROUPING SETS operators can generate the same result set as when you use UNION ALL to combine single grouping queries.

The GROUPING SETS operator can generate the same result set as that generated by using a simple GROUP BY, ROLLUP, or CUBE operator. When all the groupings that are generated by using a full ROLLUP or CUBE operator are not required, you can use GROUPING SETS to specify only the groupings that you want. 

Note: CUBE, ROLLUP and GROUPING SETS do not support the CHECKSUM_AGG function.

Create a new table:-
CREATE TABLE tbl_groupingSet
(
  area     VARCHAR(50),
  country  VARCHAR(50),
  sal      NUMERIC(18,2),
  date     DATE
)
Insert data in table:------

INSERT INTO tbl_groupingSet
SELECT 'Kanpur','India',5000,'05/01/2013'
UNION ALL
SELECT 'Delhi','India',6000,'04/01/2013'
UNION ALL
SELECT 'Udai','US',7000,'03/01/2013'

Composite Elements:
Multiple columns that are in inner parentheses in the GROUPING SETS list are treated as a single set. For example, in the clause GROUP BY GROUPING SETS ((Colum1, Column2), Column3, Column4), Column1 and Column2 are treated as one column.

Example:
In the following example, the GROUPING SETS list contains two composite elements, (area,country) and (DATEPART(yyyy,date), DATEPART(mm,date)). Each composite element is treated as one column.

SELECT area,country,DATEPART(yyyy,date),DATEPART(MM,date),SUM(sal)
FROM tbl_groupingSet
GROUP BY GROUPING SETS((area,country),(DATEPART(yyyy,date),DATEPART(MM,date)))
ORDER BY area,country,DATEPART(yyyy,date),DATEPART(MM,date)

Concatenated Elements:
When the GROUPING SETS list contains multiple sets in inner parentheses, separated by commas, the output of the sets are concatenated. The result set is the cross product or Cartesian product of the grouping sets.

In the following example, the cross product of the two ROLLUP operations is returned.

SELECT area,country,DATEPART(yyyy,date),DATEPART(MM,date),SUM(sal)
FROM tbl_groupingSet
GROUP BY ROLLUP(area,country),ROLLUP(DATEPART(yyyy,date),DATEPART(MM,date))
ORDER BY area,country,DATEPART(yyyy,date),DATEPART(MM,date)

Using GROUP BY ROLLUP

In the following example, the ROLLUP operator returns a result set that contains the following groupings:

• area, Country, DATEPART(yyyy,date),DATEPART(MM,date)
• area, Country, DATEPART(yyyy,date)
• area, Country
• area
• grand total

The number of groupings that is generated by ROLLUP is the same as the number of columns in the ROLLUP list plus a grand total grouping. The number of rows in a grouping is determined by the number of unique combinations of values in the columns of the grouping.

SELECT area,country,DATEPART(yyyy,date),DATEPART(MM,date),SUM(sal)
FROM tbl_groupingSet
GROUP BY ROLLUP(area,country,DATEPART(yyyy,date),DATEPART(MM,date))

Using GROUP BY CUBE

In the following example, the CUBE operator returns a result set that has one grouping for all possible combinations of columns in the CUBE list and a grand total grouping.

 SELECT area,country,DATEPART(yyyy,date),DATEPART(MM,date),SUM(sal)
FROM tbl_groupingSet
GROUP BY CUBE(area,country,DATEPART(yyyy,date),DATEPART(MM,date))

Using GROUP BY with GROUPING SETS

In the following example, the GROUPING SETS operator has four groupings, one for each column in the SELECT list. The operator returns one row for each unique value in the Area, Country, DATEPART(yyyy,date), and DATEPART(mm,date) columns.

SELECT area,country,DATEPART(yyyy,date),DATEPART(MM,date),SUM(sal)
FROM tbl_groupingSet
GROUP BY grouping sets(area,country,DATEPART(yyyy,date),DATEPART(MM,date))
ORDER BY area,country,DATEPART(yyyy,date),DATEPART(MM,date)

Using GROUP BY with multiple GROUPING SETS

In the following example, the GROUPING SETS list has five elements. The result set has one row for the following elements:

• Each unique combination of values in the Area and Country columns
• Each unique value in the DATEPART(yyyy,date) column
• Each unique combination of value in the DATEPART(yyyy,date) columns
• A grand total

SELECT area,country,DATEPART(yyyy,date),DATEPART(MM,date),SUM(sal)
FROM tbl_groupingSet
GROUP BY grouping sets((area,country),DATEPART(yyyy,date),DATEPART(MM,date),()) 

Using GROUPING SETS with a ROLLUP of part of the GROUP BY list

In the following example, the GROUPING SETS list includes groupings for columns Area and Country and a ROLLUP of columns DATEPART(yyyy,date) and DATEPART(mm,date).

SELECT area,country,DATEPART(yyyy,date),DATEPART(MM,date),SUM(sal)
FROM tbl_groupingSet
GROUP BY grouping sets((area,country),rollup(DATEPART(yyyy,date),DATEPART(MM,date))) 

Using GROUPING SETS with a CUBE of part of the GROUP BY list

In the following example, the GROUPING SETS list includes groupings for columns Area and Country and a CUBE of columns DATEPART(yyyy,date) and DATEPART(mm,date).

SELECT area,country,DATEPART(yyyy,date),DATEPART(MM,date),SUM(sal)
FROM tbl_groupingSet
GROUP BY grouping sets((area,country),cube(DATEPART(yyyy,date),DATEPART(MM,date)))