Subtotal summary of SQL rollup usage

Time:2019-12-14

Here is an example of SQL Server 2005:

CREATE TABLE tb(province nvarchar(10),city nvarchar(10),score int)
Insert TB select ‘Shaanxi’, ‘Xi’an’, 3
Union all select ‘Shaanxi’, ‘Ankang’, 4
Union all select ‘Shaanxi’, ‘Hanzhong’, 2
Union all select ‘Guangdong’, ‘Guangzhou’, 5
Union all select ‘Guangdong’, ‘Zhuhai’, 2
Union all select ‘Guangdong’, ‘Dongguan’, 3
Union all select ‘Jiangsu’, ‘Nanjing’, 6
Union all select ‘Jiangsu’, ‘Suzhou’, 1
GO

1. Only one summary

Select Province as province, sum (score) as score from TB group by province with roll up

Result:

Guangdong 10
Jiangsu 7
Shaanxi 9
NULL 26

Select case when grouping (province) = 1 then ‘total’ else province end as province, sum (score) as score from TB group by province with roll up

Result:

Guangdong 10
Jiangsu 7
Shaanxi 9
Total 26

2. Two levels, middle subtotal final summary

Select Province as province, city as city, sum (score) as score from TB group by province, city with roll up

Result:

Dongguan, Guangdong 3
Guangdong Guangzhou 5
Guangdong Zhuhai 2
Guangdong null 10
Nanjing, Jiangsu 6
Suzhou, Jiangsu 1
Jiangsu null 7
Shaanxi Ankang 4
Hanzhong 2, Shaanxi
Shaanxi Xi’an 3
Shaanxi null 9
NULL NULL 26

Select Province as province, city as city, sum (score) as score, grouping (province) as G? P, grouping (city) as G? C from TB group by province, city with roll up

Result:

Guangdong Dongguan 3 0 0
Guangdong Guangzhou 5 0 0
Guangdong Zhuhai 2 0 0
Guangdong null 10 0 1
Jiangsu Nanjing 6 0 0
Jiangsu Suzhou 1 0 0 0
Jiangsu null 7 0 1
Shaanxi Ankang 4 0 0
Hanzhong, Shaanxi 2 0 0
Shaanxi Xi’an 3 0 0 0
Shaanxi null 9 0 1
NULL NULL 26 1 1

Select case when grouping (province) = 1 then ‘total’ else province end province,
Case when grouping (city) = 1 and grouping (province) = 0 then ‘subtotal’ else city end city,
Sum (score) as score
         from tb group by province,city with rollup

Result:

Dongguan, Guangdong 3
Guangdong Guangzhou 5
Guangdong Zhuhai 2
Guangdong subtotal 10
Nanjing, Jiangsu 6
Suzhou, Jiangsu 1
Jiangsu subtotal 7
Shaanxi Ankang 4
Hanzhong 2, Shaanxi
Shaanxi Xi’an 3
Shaanxi subtotal 9
Total null 26