SQL Server: WITH CUBE Clause

The WITH CUBE clause is used with a GROUP BY clause to return rows that contain summary information. 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. For example, assume DBA/Developer executed the following statement:

SELECT TrxType, TrxDisposition,
COUNT(TrxID) AS TrxCount
FROM dbo.Transaction
GROUP BY TrxType, TrxDisposition
WITH CUBE;

The summary rows that represent totals for each transaction type would have a transaction type in the TrxType column, and a NULL value in the TrxDisposition column. The summary rows that represent totals for each disposition would have a disposition in the TrxDisposition column, and a NULL value in the TrxType column. The grand total summary rows would have a NULL value for both the TrxType and TrxDisposition columns.DBA/Developer should not include only the TrxID column in the GROUP BY clause and specify a grouping set that includes only an empty set, nor include only the TrxType column in DBA/Developer GROUP BY clause and create a grouping set that includes the TrxType column. Neither of these approaches would generate the desired result. DBA/Developer can use grouping sets to explicitly specify the groups for which aggregate information should displayed. Grouping sets are specified by including the GROUPING SETS clause with the GROUP BY 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 specified can contain one or more columns or an empty set, specified as (). Aggregate rows are returned in the result set for only the specified groups. Specifying an empty set indicates that a grand total row should also be returned in the result set.DBA/Developer should not include the TrxType and TrxDisposition columns in DBA/Developer GROUP BY clause and include the WITH ROLLUP option. Including the WITH ROLLUP clause would display the total number of transactions of each type and grand totals, but not the number of transactions for each disposition.