Compute and compute

The result set returned by the Group by clause only contains the total data, but does not have the original detailed records. The sum generated by Compte is used as an additional sum to list the final result set. When used together with by, compute
The clause generates a summary of control interruptions and categories in the result set.

Compute by rules:

(1) You cannot use distinct with the ROW statistics function.

(2) columns listed in compute by must appear in the selection list.

(3) cannot contain computeby
Select
Clause, because the statements including the compute clause generate irregular rows.

(4) If compute uses the by clause, order must be used.
By,
In addition, the columns in compute by must be all of the order by list, or several consecutive forward edges.

(5) Compute omitted
By, then order
It can also be omitted.

(6) computeby
When a clause contains multiple columns, A group (the first column group) is divided into several sub-groups (using the columns below) and each sub-group is counted.

(7) computeby
Clause can use multiple statistical functions, which do not affect each other.

(8) Compute does not contain
The preceding information is not grouped, but all information is counted.

(9) The summary value generated by compute is displayed as a separate result set in the query results.

Result set generated by compute

WhenCompute
TapeBy
ClauseSelectEach group of conditions has two result sets:

The first result set of each group is a detailed row set, which contains the selection list of the group.

The second result set of each group has a row, which contains the compute
The subtotal of the aggregate function specified in the clause.

WhenCompute
WithoutBy
Clause,Select
The statement has two result sets:

The first result set of each group is all the lines that contain the selected list information.

The second result set has a row, including compute
The total of Aggregate functions specified in the clause.

Comparison between compute and groupbyRelatively

Compute
And group
Differences:

Group
Generate a single result set. Each group has a row that only contains the group's aggregate functions based on the column and display the group's sub-aggregation. The selection list can only contain groups based on columns and Aggregate functions.

Compute
Generate multiple result sets. A result set contains the detailed rows of each group, including the expressions in the selection list. Another result set contains sub-aggregation of the group, or select
Total aggregation of statements. The selection list can contain expressions other than grouping based on columns or aggregate functions. Aggregate functions in compute
Clause, rather than in the selection list.

Example

Create a table as follows:

Createtable person

(Department char (10), employee
Char (6), salary int, age INT)

Insertinto person select 'A', 'zhang ',

Insertinto person select 'A', 'lil', 21

Insertinto person select 'A', 'wang ', 22

Insertinto person select 'A', 'zhao', 23

Insertinto person select 'B', 'duany', 24

Insertinto person select 'B', 'duany ',

Salary and age of Department employees

A Zhang 100 20

A Li
200 21

A Wang 300 22

A Zhao 400 23

B Duan 500 24

B Duan 600 25

(1)Group

Select
Department, employee, sum (salary)
Total, AVG (AGE) as age

Fromperson

Group
Department, employee

Result:

Salary and age of Department employees

B Duan 1100 24

A Li 200 21

A Wang 300 22

A Zhang 100 20

A Zhao 400 23

(2)Compute

Select
Department, employee, salary, age

Fromperson

Order
Department, employee

Compute sum (salary), AVG (AGE)

Result:

Salary and age of Department employees

A Li 200 21

A Wang 300 22

A Zhang 100 20

A Zhao 400 23

B Duan 500 24

B Duan 600 25

Sum AVG

2100 22

(3)ComputeBy

Select
Department, employee, salary, age

Fromperson

Order
Department, employee

Compute sum (salary), AVG (AGE)
By department

Result

Salary and age of Department employees

A Li 200 21

A Wang 300 22

A Zhang 100 20

A Zhao 400 23

Sum AVG

1000 21

Salary and age of Department employees

B Duan 500 24

B Duan 600 25

Sum AVG

24, 1100