Aggregate functions cube and Rollup

Let's take a look at some explanations of the 2005 help:

 

Aggregate Function. When a row is added by the cube or rollup operator, the output value of the appended column is 1. When a row is not added by the cube or rollup operator, this function will cause the output value of the appended column to be 0.

Grouping is allowed only in the selection list associated with the group by clause that contains the cube or rollup operator.

Syntax

Grouping (column_name) <over clause>

Remarks

Grouping is used to distinguish the null values returned by the cube and rollup generated by the standard null values. A special application that returns NULL as the result of a cube or rollup operation. It acts as a placeholder for the column in The result set, indicating the whole.

Parameters

Column_name

Column in the group by clause, used to test the null value of cube or rollup.

<Over_clause>

Divide the result set generated by the from clause into partitions or windows that apply the ranking window or aggregate window function.

Return type

Int

ExampleXmlns = "http://ddue.schemas.microsoft.com/authoring/2003/5" 2003 authoring ddue.schemas.microsoft.com 5: content>

The following example groupsSalesquotaAnd AggregateSaleytdQuantity.GroupingFunction ApplicationSalesquotaColumn.

	CopyCode
Use adventureworks; goselect salesquota, sum (salesytd) 'totalsalesytd ', grouping (salesquota) as 'grouping' from sales. salespersongroup by salesquota with rollup; go

Result set inSalesquotaThe following two null values are displayed. FirstNullRepresents the null group obtained from this column in the table. SecondNullLocated in the summary row added by the rollup operation. Show all summary rowsSalesquotaGroupTotalsalesytdNumber, andGroupingColumn1.

The following is the result set:

	Copy code
Salesquota totalsalesytd grouping --------- ----------- -------- null 1533087.5999 0250000.00 33461260.59 0300000.00 9299677.9445 0 null 44294026.1344 1 (4 row (s) affected)

2003 authoring ddue.schemas.microsoft.com 5: content>

 

The following is a small example:

 

Code

/*
* Author: Dong Guangxiang
* Script Date: 2009/6/28
*/
With cte_temp (name, sex, ages)
As (
Select 'zhao ', 'male', 25
Union all
Select 'money', 'male', 30
Union all
Select 'sun', 'female, 26
Union all
Select 'lil', 'female, 32
)
-- Select * From cte_temp

Select name = case when grouping ([name]) = 1
And grouping (sex) = 0 then 'Total'
When grouping ([name]) = 1
And grouping (sex) = 1 then 'Total'
Else [name]
End,
Sex = isnull (sex ,''),
Ages = sum (AGEs) -- sum
From cte_temp
Group by sex, name with Rollup

/* Select name = case when grouping ([name]) = 1
And grouping (sex) = 0 then 'Total'
When grouping ([name]) = 1
And grouping (sex) = 1 then 'Total'
When grouping ([name]) = 0
And grouping (sex) = 1 then [name] + 'Total'
Else [name]
End,
Sex = isnull (sex ,''),
Ages = sum (AGEs) -- sum
From cte_temp
Group by sex, name with cube */V

 

Total query result (rollup ):

Name sex ages
-------------------
Qian male 30
Zhao male 25
Total Male 55
Li female 32
Sun female 26
Total female 58
Total 113

Sum query result (cube ):

Name sex ages
---------------------
Qian male 30
Zhao male 25
Total Male 55
Li female 32
Sun female 26
Total female 58
Total 113
Li total 32
Total money 30
Sun total 26
Zhao totaling 25

Conclusion: The preceding example shows that rollup is used to group and summarize a column, while cube is used to group and summarize all columns contained in group, the name with cube column is also summarized in a group, which is the same as the rollup summary ......

From: http://www.cnblogs.com/myssh/archive/2009/06/28/1512691.html