Use of summary functionality in SQL Server Grouping,rollup and Cube_mssql

The first time to see such a SQL statement, do not understand, which used the following less common

Aggregate functions: GROUPING

Operator used to summarize data: ROLLUP

SELECT

Case GROUPING (O.customerid) when 0 THEN o.customerid ELSE ' [Total] ' End

As Allcustomerssummary,

Case GROUPING (Od.orderid) at 0 THEN Od.orderid ELSE-1 end

As Individualcustomersummary,

SUM (Od.quantity*od.unitprice) as Price

From Orders o, [order Details] OD

WHERE year (o.orderdate) = 1998 and Od.orderid=o.orderid

GROUP by O.customerid, Od.orderid with ROLLUP

ORDER BY Allcustomerssummary

To view the help of SQL Server to find that, ah, there is such a powerful thing, not from the recall before the Crystal Report, the original in SQL Server can achieve such a function.

1. Summarize data with Cube

The cube operator produces a result set that is a multidimensional dataset. A cube is an extension of the fact data, which is the data that records individual events. The extension is based on the columns that the user intends to analyze. These columns are called dimensions. A cube is a result set that contains a crosstab table of all possible combinations of each dimension.

The CUBE operator is specified in the GROUP by clause of a SELECT statement. The select list of the statement should contain dimension columns and aggregate function expressions. GROUP by should specify dimension columns and keywords with CUBE. The result set will contain all possible combinations of the values in the Dimension column and the aggregate values in the underlying rows that match the combination of these dimension values.

For example, a simple table Inventory contains:

Item         Color        Quantity          ------------------------------------------------------------------Table        Blue         124            Table        Red         223            Chair        Blue            Chair        Red         210            

The following query returns a result set that will contain Quantity subtotals for all possible combinations of Item and Color :

SELECT Item, Color, SUM (Quantity) as Qtysumfrom inventorygroup by Item, color with CUBE

Here is the result set:

Item         Color        qtysum           ------------------------------------------------------------------Chair        Blue         101.00           Chair        Red         210.00           Chair        (null)        311.00           table        Blue         124.00           table        Red         223.00           Table (NULL) 347.00 (NULL) (NULL) 658.00 (NULL)        Blue         225.00           (null )        Red         433.00           

We focus on the following lines:

Chair        (null)        311.00           

This line reports subtotals for all rows in the Item dimension that have a value of Chair. A NULL value is returned for the color dimension, indicating that the aggregation reported by the row includes rows that have a color dimension of any value.

Table        (null)        347.00           

This line is similar, but reports subtotals for all rows in the Item dimension that have a value of Table.

(null)        (null)        658.00           

This row reports the totals for the cube. The value of Item and Color dimensions is NULL, which indicates that all values in the two dimensions are summarized in the row.

(null)        Blue         225.00           (null)        Red         433.00           

These two rows report subtotals for a Color dimension. The item dimension value in both rows is NULL, which indicates that the aggregated data comes from a row that has an arbitrary value for the item dimension.

Using GROUPING to differentiate between null values

The null value generated by the cube operation poses a problem: how to distinguish between null values generated by the cube operation and null values returned from the actual data? This problem can be solved using the GROUPING function. If the value in the column comes from the fact data, the GROUPING function returns 0, or 1 if the value in the column is NULL generated by the CUBE operation. In the CUBE operation, the generated NULL represents the whole value. You can write a SELECT statement using the GROUPING function to replace the generated NULL with the string all. Because null in fact data indicates that the data value is unknown, the SELECT statement can also be decoded to return a string UNKNOWN to replace NULL from the fact data. For example:

SELECT Case When (GROUPING (item) = 1) THEN ' all '      ELSE ISNULL (item, ' UNKNOWN ') end    as item, case when    (GROUPING (color) = 1) THEN ' all '      ELSE ISNULL (color, ' UNKNOWN ') end    as color,    SUM (Quantity) as Qtysumfrom Inventorygrou P by Item, Color with CUBE

Multidimensional Data set

The cube operator can be used to generate an n -dimensional cube, a cube that has any number of dimensions. A cube with only one dimension can be used to generate totals, for example:

SELECT Case When (GROUPING (item) = 1) THEN ' all '      ELSE ISNULL (item, ' UNKNOWN ') end    as item,    SUM (Quantity) as Qt Ysumfrom Inventorygroup by Item with Cubego

The result set returned by this SELECT statement shows subtotals for each value in item as well as totals for all values in the item :

Item         qtysum           ----------------------------------------------Chair        311.00           Table        347.00           all         658.00           

A SELECT statement that contains a cube with many dimensions can produce a large result set, because these statements will be rows for all combinations of values in all dimensions. These large result sets may contain too much data to be easily read and understood. One solution to this problem is to put the SELECT statement in the view:

CREATE VIEW Invcube asselect case When (GROUPING (item) = 1) THEN "All"      ELSE ISNULL (item, ' UNKNOWN ') end as    item,
  case When (GROUPING (color) = 1) THEN ' all '      ELSE ISNULL (color, ' UNKNOWN ') end    as color,    SUM (Quantity) as Qty Sumfrom Inventorygroup by Item, Color with CUBE

You can then use this view to query only the dimension values that you are interested in:

SELECT *from invcubewhere item = ' Chair ' and color = ' All ' item         Color        qtysum           ------------------------------- -----------------------------------Chair        all         311.00           (1 row (s) affected)

2. Summarize data with ROLLUP

The ROLLUP operator is useful when generating reports that contain subtotals and totals. The ROLLUP operator produces a result set similar to the result set generated by the cube operator. For more information.

The difference between CUBE and ROLLUP is:

• Cube produces a result set that shows aggregates for all combinations of values in the selected column.
  
  
• The result set generated by ROLLUP shows the aggregation of a hierarchy of values in the selected column.

For example, the simple table Inventory contains:

Item         Color        Quantity          ------------------------------------------------------------------Table        Blue         124            Table        Red         223            Chair        Blue            Chair        Red         210            

The following query generates a subtotal report:

SELECT Case When (GROUPING (item) = 1) THEN ' all '      ELSE ISNULL (item, ' UNKNOWN ') end    as item, case when    (GROUPING (color) = 1) THEN ' all '      ELSE ISNULL (color, ' UNKNOWN ') end    as color,    SUM (Quantity) as Qtysumfrom Inventorygrou P by Item, color with rollupitem         color        qtysum           --------------------------------------------------------- ---------Chair        Blue         101.00           Chair        Red         210.00           Chair        all         311.00           Table        Blue         124.00           table        Red         223.00           table        all         347.00         All 658.00           (7 row (s) affected)

If the ROLLUP keyword in the query is changed to cube, the cube result set is the same as the result, except that the following two rows are returned at the end of the result set:

All         Blue         225.00           all         Red         433.00           

The CUBE operation is a possible combination of the values in Item and Color . For example, the CUBE not only reports all possible combinations of color values that are combined with the Item value Chair (red, blue, and Red + blue), but also reports a combination of color value Red all possible combinations of 5>item values (Chair, table, and Chair + table).

For each value in the column to the right of the GROUP by clause, the ROLLUP operation does not report all possible combinations of values in the left column (or column on the left). For example, ROLLUP does not report all possible combinations of Item values for each Color value.

The result set of the ROLLUP operation has a function similar to the result set returned by the COMPUTE by; however, ROLLUP has the following advantages:

• ROLLUP returns a single result set, COMPUTE by returns multiple result sets, and multiple result assemblies increase the complexity of the application code.
  
  
• ROLLUP can be used in server cursors; COMPUTE by is not available.
  
  
• Sometimes, the query optimizer generates more efficient execution plans for ROLLUP than for COMPUTE by.

3.GROUPING

is an aggregate function that produces an additional column, and when a row is added with the CUBE or ROLLUP operator, the additional column output value is 1, and the additional column value is 0 when the added row is not generated by cube or ROLLUP.

Grouping is allowed only in select lists that are associated with the GROUP BY clause that contains the CUBE or ROLLUP operator.

Grammar

GROUPING ( column_name )

Parameters

column_name

is the column in the GROUP by clause that is used to check for CUBE or ROLLUP null values.

return type

int

Comments

Grouping is used to differentiate between null values returned by the CUBE and ROLLUP and standard null values. A special application that returns NULL as a result of a cube or ROLLUP operation. It acts as a placeholder for the column in the result set, meaning "all".

Example

The following example groups the numeric values of the royalty and aggregates the advance values. The GROUPING function is applied to the Royalty column.

USE pubsSELECT royalty, SUM(advance) 'total advance',  GROUPING(royalty) 'grp'  FROM titles  GROUP BY royalty WITH ROLLUP

The result set displays two null values under royalty . The first null represents the set of empty values that are obtained from this column in the table. The second NULL is in the Total row added by the ROLLUP operation. The total row shows the advance aggregate values for all royalty groups, and 1 is identified in the GRP column.

Here is the result set:

royalty    total advance       grp ---------   ---------------------  ---NULL      NULL           0 10       57000.0000        0 12       2275.0000        0 14       4000.0000        0 16       7000.0000        0 24       25125.0000        0 NULL      95400.0000        

3.GROUPING

is an aggregate function that produces an additional column, and when a row is added with the CUBE or ROLLUP operator, the additional column output value is 1, and the additional column value is 0 when the added row is not generated by cube or ROLLUP.

Grouping is allowed only in select lists that are associated with the GROUP BY clause that contains the CUBE or ROLLUP operator.

Grammar

GROUPING ( column_name )

Parameters

column_name

is the column in the GROUP by clause that is used to check for CUBE or ROLLUP null values.

return type

int

Comments

Grouping is used to differentiate between null values returned by the CUBE and ROLLUP and standard null values. A special application that returns NULL as a result of a cube or ROLLUP operation. It acts as a placeholder for the column in the result set, meaning "all".

Example

The following example groups the numeric values of the royalty and aggregates the advance values. The GROUPING function is applied to the Royalty column.

USE pubsSELECT royalty, SUM(advance) 'total advance',  GROUPING(royalty) 'grp'  FROM titles  GROUP BY royalty WITH ROLLUP

The result set displays two null values under royalty . The first null represents the set of empty values that are obtained from this column in the table. The second NULL is in the Total row added by the ROLLUP operation. The total row shows the advance aggregate values for all royalty groups, and 1 is identified in the GRP column.

Here is the result set:

royalty    total advance       grp ---------   ---------------------  ---NULL      NULL           0 10       57000.0000        0 12       2275.0000        0 14       4000.0000        0 16       7000.0000        0 24       25125.0000        0 NULL      95400.0000        

3.GROUPING

is an aggregate function that produces an additional column, and when a row is added with the CUBE or ROLLUP operator, the additional column output value is 1, and the additional column value is 0 when the added row is not generated by cube or ROLLUP.

Grouping is allowed only in select lists that are associated with the GROUP BY clause that contains the CUBE or ROLLUP operator.

Grammar

GROUPING ( column_name )

Parameters

column_name

is the column in the GROUP by clause that is used to check for CUBE or ROLLUP null values.

return type

int

Comments

Grouping is used to differentiate between null values returned by the CUBE and ROLLUP and standard null values. A special application that returns NULL as a result of a cube or ROLLUP operation. It acts as a placeholder for the column in the result set, meaning "all".

Example

The following example groups the numeric values of the royalty and aggregates the advance values. The GROUPING function is applied to the Royalty column.

USE pubsSELECT royalty, SUM(advance) 'total advance',  GROUPING(royalty) 'grp'  FROM titles  GROUP BY royalty WITH ROLLUP

The result set displays two null values under royalty . The first null represents the set of empty values that are obtained from this column in the table. The second NULL is in the Total row added by the ROLLUP operation. The total row shows the advance aggregate values for all royalty groups, and 1 is identified in the GRP column.

Here is the result set:

royalty    total advance       grp ---------   ---------------------  ---NULL      NULL           0 10       57000.0000        0 12       2275.0000        0 14       4000.0000        0 16       7000.0000        0 24       25125.0000        0 NULL      95400.0000        

The introduction to Grouping,rollup,cube comes from the help of the Chinese version of SQL Server2000.