GROUPING .... With rollup for group summary statistics

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          0NULL           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)
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
－－－－－－－－－－－－－－－－－－－－－－－－－－－－
WITH ROLLUP
----------------------------
First, create a test table and add data.
Create table # t (a int, B int, c int, d int, e int)
Insert into # t values (1, 2, 3, 4, 5)
Insert into # t values (1, 2, 3, 4, 6)
Insert into # t values (1, 2, 3, 4, 7)
Insert into # t values (1, 2, 3, 4, 8)
Insert into # t values (1, 3, 3, 4, 5)
Insert into # t values (1, 3, 3, 4, 6)
Insert into # t values (1, 3, 3, 4, 8)
Insert into # t values (1, 3, 3, 4, 7)
 
Insert into # t values (2, 2, 4, 5)
Insert into # t values (2, 2, 3, 4, 6)
Insert into # t values (2, 2, 4, 7)
Insert into # t values (2, 2, 5, 4, 8)
Insert into # t values (2, 3, 6, 4, 5)
Insert into # t values (2, 3, 4, 6)
Insert into # t values (2, 3, 4, 8)
Insert into # t values (2, 3, 4, 7)
Scenario 1: Only one aggregate is required when there is only one category summary column. You only need to add with rollup.
Select case when grouping (a) = 1 then' total 'else cast (a as varchar) end,
Sum (B), sum (c), sum (d), sum (e) from # t group by a with rollup
Case 2: There are multiple Classification summary columns. You only need one total. After adding rollup, you need to add a judgment.
Select case when grouping (a) = 1 then' total 'else cast (a as varchar) end,
B,
Sum (c), sum (d), sum (e) from # t
Group by a, B with rollup
Having grouping (B) = 0 or grouping (a) = 1
Select case when grouping (a) = 1 then' total 'else cast (a as varchar) end,
B,
C,
Sum (d), sum (e) from # t
Group by a, B, c with rollup
Having grouping (c) = 0 or grouping (a) = 1
Case 3: There are multiple Classification summary columns, and all subtotal and total are required.
Select case when grouping (a) = 1 then' total 'else cast (a as varchar) end,
Case when grouping (B) = 1 and grouping (a) = 0 then 'subtotal 'else cast (B as varchar) end B,
Case when grouping (c) = 1 and grouping (B) = 0 then 'subtotal 'else cast (c as varchar) end c,
Sum (d), sum (e) from # t
Group by a, B, c with rollup
Another way to display Subtotal
Select case when grouping (a) = 1 then 'Total'
When grouping (B) = 1 then cast (A as varchar) + 'subtotal'
Else cast (A as varchar) end,
Case when grouping (B) = 0 and grouping (c) = 1
Then cast (B as varchar) + 'subtotal 'else cast (B as varchar) end B,
Case when grouping (c) = 1 and grouping (B) = 0
Then ''else cast (C as varchar) end C,
Sum (D), sum (e) from # T
Group by a, B, c with Rollup
Case 4: there are multiple Classification summary columns that require partial subtotal and total
Select case when grouping (A) = 1 then' total 'else cast (A as varchar) end,
B,
Case when grouping (c) = 1 and grouping (B) = 0 then 'subtotal 'else cast (C as varchar) end C,
Sum (D), sum (e) from # T
Group by a, B, c with Rollup
Having grouping (A) = 1 or grouping (B) = 0
Select case when grouping (A) = 1 then' total 'else cast (A as varchar) end,
Case when grouping (B) = 1 and grouping (A) = 0 then 'subtotal 'else cast (B as varchar) end B,
C,
Sum (D), sum (e) from # T
Group by a, B, c with Rollup
Having grouping (A) = 1 or grouping (B) = 1 or grouping (c) = 0