Usage of the grouping function in SQL Server

ArticleDirectory

• Group aggregation using grouping

Online help from SQL Server

 
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:

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

  
 
 
Group aggregation using grouping

When SQL is used, we often encounter such a problem that requires grading and calculating the sum. Do you often worry about how to classify statistics in a table? Here we can use the grouping () function to solve this problem.
 
 
 
 
The following example shows a data table of Administrative Region, unit, and sales.
-- Create a table and insert data
Create Table t_sendmoney (statecode varchar (6), depcode varchar (6), sendmoney money Money)
Insert into t_sendmoney
Select '20180101', '20180101', 100001
Union all
Select '20180101', '20180101', 100001
Union all
Select '20180101', '20180101', 100001
Union all
Select '20180101', '20180101', 100002
Union all
Select '20180101', '20180101', 100002
Union all
Select '20180101', '20180101', 100003
Union all
Select '20180101', '20180101', 100003
Union all
Select '20180101', '20180101', 100004
-- Achieve hierarchical Summary of data by grouping
Select
Case when grouping (statecode) = 1 then 'total: 'else statecode end as statecode
, Case when grouping (depcode) = 1 then 'State Total: 'else depcode end as depcode
, Sum (sendmoney) as sendmoney
From t_sendmoney
Group by statecode, depcode with Rollup
-- Query Result
Statecode depcode sendmoney
-----------------------------------------
100001 310001 3000.00
100001 310002 1500.00
100001 State total: 4500.00
100002 320001 4200.00
100002 State total: 4200.00
100003 330001 1800.00
100003 330002 2100.00
100003 State total: 3900.00
100004 340001 2500.00
100004 State total: 2500.00
Total: State total: 15100.00