SQL rollup usage subtotal Summary

Here is an example of SQL server2005:

Create table tb (province nvarchar (10), city nvarchar (10), score int)
INSERT tb SELECT 'shaanxi ', 'xiance', 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. There is only one summary

Select province as province, sum (score) as score from tb group by province with rollup

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 rollup

Result:

Guangdong 10
Jiangsu 7
Shaanxi 9
Total 26

2. Two levels, summary of the middle Subtotal

Select province as province, city as city, sum (score) as score from tb group by province, city with rollup

Result:

Dongguan, Guangdong 3
Guangzhou, Guangdong 5
Zhuhai, Guangdong 2
Guangdong NULL 10
Nanjing, Jiangsu Province 6
Jiangsu Suzhou 1
Jiangsu NULL 7
Ankang, Shaanxi 4
Hanzhong, Shaanxi 2
Xi'an, Shaanxi 3
Shaanxi NULL 9
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 rollup

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
Jiangsu NULL 7 0 1
Shaanxi Ankang 4 0 0
Hanzhong, Shaanxi Province 2 0 0
Xi'an, Shaanxi 3 0 0
Shaanxi NULL 9 0 1
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
Guangzhou, Guangdong 5
Zhuhai, Guangdong 2
Subtotal, Guangdong 10
Nanjing, Jiangsu Province 6
Jiangsu Suzhou 1
Jiangsu subtotal 7
Ankang, Shaanxi 4
Hanzhong, Shaanxi 2
Xi'an, Shaanxi 3
Subtotal, Shaanxi 9
Total NULL 26