Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to summarize subtotals with rollup". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Here is a usage example in sql server2005:

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. There is only one summary

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

Results:

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

Results:

Guangdong 10

Jiangsu 7

Shaanxi 9

Total 26

2. The final summary of the two levels and the middle subtotal

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

Results:

Guangdong Dongguan 3

Guangdong Guangzhou 5

Guangdong Zhuhai 2

Guangdong NULL 10

Nanjing, Jiangsu 6

Jiangsu Suzhou 1

Jiangsu NULL 7

Shaanxi Ankang 4

Shaanxi Hanzhong 2

Shaanxi Xi'an 3

Shaanxi NULL 9

NULL NULL 26

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

Results:

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

Suzhou, Jiangsu 1 0 0

Jiangsu NULL 7 0 1

Shaanxi Ankang 4 0 0

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

Results:

Guangdong Dongguan 3

Guangdong Guangzhou 5

Guangdong Zhuhai 2

Guangdong subtotal 10

Nanjing, Jiangsu 6

Jiangsu Suzhou 1

Jiangsu subtotal 7

Shaanxi Ankang 4

Shaanxi Hanzhong 2

Shaanxi Xi'an 3

Shaanxi subtotal 9

Total NULL 26

This is the end of the content of "how to summarize subtotals with rollup". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.