## Posts Tagged ‘t-sql’

## SQL Server / T-SQL: ROLLUP(), CUBE(), GROUPING_SETS()

Note: Much of this was just pulled from the language manual; still, I find it a useful extract with and I’ve added a little commentary.

ROLLUP ( )Generates the simple GROUP BY aggregate rows, plus subtotal or super-aggregate rows, and also a grand total row.

The number of groupings that is returned equals the number of expressions in the

SELECT a, b, c, SUM ( <expression> ) FROM T GROUP BY ROLLUP (a,b,c); One row with a subtotal is generated for each unique combination of values of (a, b, c), (a, b), and (a). A grand total row is also calculated. Columns are rolled up from right to left. The column order affects the output groupings of ROLLUP and can affect the number of rows in the result set. CUBE ( )Generates simple GROUP BY aggregate rows, the ROLLUP super-aggregate rows, and cross-tabulation rows. CUBE outputs a grouping for all permutations of expressions in the <composite element list>. The number of groupings that is generated equals (2n), where n = the number of expressions in the <composite element list>. For example, consider the following statement. SELECT a, b, c, SUM (<expression>) FROM T GROUP BY CUBE (a,b,c);

One row is produced for each unique combination of values of (a, b, c), (a, b), (a, c), (b, c), (a), (b) and (c) with a subtotal for each row and a grand total row.

Column order does not affect the output of CUBE.

GROUPING SETS ( )Specifies multiple groupings of data in one query. Only the specified groups are aggregated instead of the full set of aggregations that are generated by CUBE or ROLLUP. The results are the equivalent of UNION ALL of the specified groups. GROUPING SETS can contain a single element or a list of elements. GROUPING SETS can specify groupings equivalent to those returned by ROLLUP or CUBE. The

( )The empty group generates a total.