SQL Server: GROUPING SETS Clause

The GROUPING SETS clause allows DBA/Developer to explicitly specify the groups for which aggregate information should be displayed. This allows DBA/Developer to use more than one grouping within a single query. The syntax of the GROUP BY clause with a GROUPING SETS clause is as follows:

GROUP BY GROUPING SETS

(groupingset1 [,...groupingsetn])

Each grouping set can contain one or more columns or an empty set. Aggregate rows are returned in the result set for only the specified groups. Specifying an empty set, with (), indicates that a grand total row should also be returned in the result set. In this statement, DBA/Developer specified an empty set in the GROUPING SETS clause. However, DBA/Developer used the GROUPING_ID function in the HAVING clause. The GROUPING_ID function returns either 0 or a 1 to identify the level of grouping. This HAVING clause suppresses the grand total rows. DBA/Developer can also use the CUBE and ROLLUP operators to aggregate data. The ROLLUP operator groups the selected rows in the result set based on the values in the GROUP BY clause and returns one row as a summary row for each group. The ROLLUP operator can be used to generate totals and subtotals. Using the ROLLUP operator, a row containing the subtotal and the total is also returned in the result set. When DBA/Developer specifies WITH CUBE, a summary row is included in the result set for each possible combination of the columns specified in the GROUP BY clause. When using ROLLUP, CUBE, or GROUPING SETS, aggregate rows can be identified by the NULL values. Summary rows for grand totals will contain NULL values for all grouping columns. If DBA/Developer grouped using two columns, such as ProductID and SpecialOfferID in this scenario, DBA/Developer could identify the aggregate rows as follows:

Summary rows that represent totals for each ProductID would have a value for ProductID and a NULL value for SpecialOfferID.

Summary rows that represent totals for each SpecialOfferID would have a value for SpecialOfferID and a NULL value for ProductID.

DBA/Developer should not remove the empty grouping set from the GROUPING SETS clause. To include grand totals in a query that uses the GROUPING SETS clause, DBA/Developer must include an empty set. DBA/Developer should not modify the HAVING clause to check for a value of 1 returned by the GROUPING_ID function. This would return only the grand total row, and filter out the aggregate rows for products and the aggregate rows for unique ProductID and SpecialOfferID combinations.DBA/Developer should not remove the GROUP BY and HAVING clauses and use GROUP BY SpecialOfferID WITH ROLLUP in the GROUP BY clause. All columns in the SELECT list must either use an aggregate function or be included in the query's GROUP BY clause.