17. Computing total data of SQL Server summary data

SQL Server summary data computing total data

Although the reportin service can easily calculate the total and subtotal, no additional work is required for queries. However

Practice:ProgramProvide a total so that it can be useful to display it at the bottom of the form or webpage.

The following three aggregation commands will provide a good solution.

I. rollup and cube

The basic syntaxes are the same:

Group ....

With rollup | cube

The aggregate functions rollup and cube calculate the subtotal and total in an independent row, and include

Contains null values to indicate the corresponding values as total. Rollup generates groups based on the column's subtotal and total rows, while cube expands

This function generates total and subtotal rows for each group based on the column. There is a function named grouping () that returns true (1) in the total or small number of rows ), otherwise, 0 is returned.

For example:

-- Find out the salary and group it by Department to obtain the total Subtotal

Select

Case grouping (C. bmname)

When 0 then C. bmname

When 1 then 'total sales'

End as department name

, Sum (A. basic_gz + A. jiaban_gz + A. Jiangjin) Department salary from gongzi

Inner join yuangong B on A. ygid = B. ID

Inner join bumen C on B. bmid = C. ID

Group by C. bmname

With Rollup

-- If group by has multiple columns, the subtotal is returned, not just the total.

Result:

Department name department salary

Department 702

Technology 5469

Customer Department 1878

Sales 2200

Total wages: 10249

 

 

Grouping is based on multiple columns:

-- Find out the salary and group it by Department to obtain the total Subtotal

Select

Case grouping (C. bmname)

When 0 then C. bmname

When 1 then 'total sales'

End as department name

, Sum (A. basic_gz + A. jiaban_gz + A. Jiangjin) Department salary from gongzi

Inner join yuangong B on A. ygid = B. ID

Inner join bumen C on B. bmid = C. ID

Group by C. ID, C. bmname

With Rollup

-- If group by has multiple columns, the subtotal is returned, not just the total.

Result:

Department name department salary

Technology 5469

Total wages: 5469

Department 702

Total wages: 702

Sales 2200

Total wages: 2200

Customer Department 1878

Total wages: 1878

Total wages: 10249

Note: The rollup is placed behind the group by clause, and the SQL server command generates a total line.

If cube is used, the display of all the total and subtotal rows is placed at the end, and subtotal groups like rollup are supported.

Directly following the group. This is an extension of rollup.

Ii. compute and compute

 

The compute clause does not create an aggregate query, but adds an aggregate query after a general query. This query

Returns a general result set containing a detailed data row, and then adds a few rows, which contains the summary information of the result set.

Compute aggregate function (column name 1), aggregate function (column name 2) [by] group by Column

Note: When 'by' is added, the group subtotal is available, not just the total, and cannot be used together with 'group by'. This is

Backward compatible. Generally, rollup and CuBE are used.

3. Create a cross tabulation Query

Although aggregate queries can be grouped by multiple columns, it is not convenient to view data quickly because the results are arranged by different branches.

. Cross tabulation queries rotate the group 90 ° counter-clockwise Based on the column (or one-dimensional) and convert it into a column in the result set.

For example:

CATEGORY South East West Total

X 100 0 20 100

Y 200 300 50 550

Z 0 0 100 100

This is a grouping by type, where each group calculates the sales volume of each region of this category,

And the total sales volume of this type.

Limitations: group by queries arranged by branches can have multiple Aggregate functions, while cross queries can only display one type.

Measurement method (only one aggregate function can be calculated ).

Note: The term "cross tabulation query" describes the appearance of the result set, rather than the method for creating a cross table.

There are multiple ways to generate a crosstab chart.

4. Fixed column cross tabulation Query

There are three methods for creating a query that contains a known fixed column cross table.

1. Use related subqueries. 2. Use the case expression

3. Use perspective

1. Use related subqueries

Poor performance. A subquery is executed for each group based on each measurement method of the column.

For example:

Select R. category,

(Select sum (amount) from rawdata where region = 'south' and

Category = R. Category) as 'south ',

(Select sum (amount) from rawdata where region = 'North' and

Category = R. Category) as 'north ',

(Select sum (amount) from rawdata where region = 'east' and

Category = R. Category) as 'east ',

(Select sum (amount) from rawdata where region = 'west' and

Category = R. Category) as 'west ',

Sum (amount) as total

From rawdata R

Group by category

2. Use the case expression

Use the case expression to filter the data to be summarized, instead of filtering data in the related subquery.

The query engine can process the entire cross tabulation query as a single dataset-based operation.

Note: it is the most suitable and convenient method for creating cross tabulation queries.

For example:

Select R. category,

Sum (Case region when 'south' then amount else 0 end) as South,

Sum (Case region when 'north' then amount else 0 end) as North,

Sum (Case region when 'east' then amount else 0 end) as East,

Sum (Case region when 'west' then amount else 0 end) as West,

Sum (amount) as total

From rawdata R

Group by category

Order by category

This query does not use any where clause to filter data in the rawdata table.

Do not divide the dataset. Then execute the aggregate function to create a result row for each category.

Note: This query uses a trick and uses a case expression in sum (), so that each column only considers

The value of a region.

3. Use perspective

In this way, the aggregate function is executed in the from clause. Create a crosstab chart and use it as the data source,

For example, if you think of struct as a table value function as a data source, it accepts two parameters.

The first parameter is an aggregate function used to calculate the value of the cross table. The first parameter lists

.

Basic Syntax:

(Data Source)

Bytes

(Aggregate function)

Condition

5. Dynamic cross tabulation Query

Use cursor and cursor.