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