T-SQL syntax: GROUP BY clause grouping sets, CUBE, ROLLUP

GROUP BY clause

1, ROLLUP ()

A small subtotals that generates a grouping of a dimension, and also generates a total row.

Sample table:

Select   from Student

Let's take a look at the specific examples:

Select sex,sclass,sumfromgroup by Rollup (Sex,sclass)

As shown in, ROLLUP () is (sex,sclass), each unique combination of (sex) produces a row with subtotals, and a total row. This can be seen from right-to-left sequential summarization, the order of the columns will affect the output grouping of rollup, the result set will also be affected

2, CUBE ()

Generates all the small subtotals of all the cubes in the cube expression, along with a total row.

Specific examples:

Select sex,sclass,sumfrom thegroup by Cube (sex,sclass)

　　

As shown in: CUBE () is (sex,sclass), (sex), each combination of (Sclass) produces a small subtotals, and a total row. The order of the columns also does not affect the output of the result

3, GROUPING sets ()

Specifying multiple groupings of data in a single query is equivalent to combining multiple group by.

A.group by Union

Select sex,null sclass,sumfromgroup by sex  Union all selectnull sex,sclass,sumfromgroupby Sclass　　

Results:

B.grouping Sets ()

Select sex,sclass,sumfromgroupbygrouping sets (Sclass,sex)

Results:

The results of both queries are the same!

Auxiliary functions

There are two more important functions grouping () and grouping_id (), both of which are used to differentiate between standard values and Null values returned by rollup, cube, or grouping sets.

1, GROUPING ()

In the result set, if grouping () returns 1, it represents a null value produced by an aggregate clause, and 0 indicates a null value in the original record.

Example:

Select  Case  when Grouping (Sex) = ' 1 '  Then ' N /A ' Else End, Sclass,sum(score)from thegroup by Cube (Sclass, Sex

Results:

2, grouping_id ()

A function that calculates the grouping level, the column of grouping_id (column 1, column 2) must be included in the group By column expression, and grouping_id () will join GROUPING () in each output row for the corresponding value returned by each column in its column list as 0, 1 strings.

The stitched string is then interpreted as a binary number and the corresponding decimal integer is returned. Formula:

Example:

Select sex,sclass,sumfromgroupbygrouping sets (Sclass,sex)

Results:

Let's look at the first row of data in the result set, grouping_id (Sclass,sex) =grouping (sclass) *2^1+grouping (Sex) *2^0

In the first row of data, the grouping (sclass) result is 1, grouping (sex) result is 0, the concatenation of the binary data is 10,grouping_id (sclass,sex) =1*2^1+0*2^0 result is 2, The grouping_id () in each row is computed in this way.

Summarize:

1. The cube clause can be written as a with CUBE,ROLLUP clause that can be written with rollup, and the syntax for the WITH cube and with rollup in the SQL Server Help document will be removed in a future release, and will not be used with this notation;
2. The grouping () and grouping_id () functions can specify more specific meanings for grouped data.

T-SQL syntax: GROUP BY clause grouping sets, CUBE, ROLLUP