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 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 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