SQL compute by Usage Analysis

GROUPThe disadvantage of a clause is that the returned result set only contains the total data without the original detailed records. To do this in SQL SERVER, you can use the COMPUTE BY clause. The sum generated by COMPTE is used as an additional sum to list the final result set. When used together with BY, the COMPUTE clause generates a summary of control interruptions and categories in the result set.

The following SELECT statement uses a simple COMPUTE clause to generate the sum of price and advance in the titles table:
Copy codeThe Code is as follows:
USE pubs
SELECT type, price, advance
FROM titles
Order by type
Compute sum (price), SUM (advance)

The following query adds an optional BY keyword to the COMPUTE clause to generate a subtotal for each group:

USE pubs
Copy codeThe Code is as follows:
SELECT type, price, advance
FROM titles
Order by type
Compute sum (price), SUM (advance) BY type

The results of this SELECT statement are returned with 12 result sets. Each of the six groups has two result sets. The first result set of each group is a row set that contains the information requested in the selection list. The second result set of each group contains the subtotal of the two SUM functions in the COMPUTE clause.

Rules of the compute by clause:

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

(2) compute ??? By clause ??? The output column must appear in the selection list.

(3) The select into clause cannot be used in statements containing the compute by clause, because the statements including the compute clause generate irregular rows.

(4) If the compute by clause is used, the order by clause must be used, and the columns in the compute by clause must be included in the order by clause, in addition, the order of the column and the start item must be the same (to put it bluntly, the column in the compute by clause must be the whole list in the order by clause, or the number of consecutive sides in the Front ).

(5) If compute omit by, order by can also be omitted.

(6) If the compute by clause contains multiple columns, A group (the group in which the first column is divided) is divided into several sub-groups (the following columns are used ), the sub-groups at each layer are counted.

(7) When multiple compute by clauses are used, the results are calculated based on different groups. The detailed information is displayed in the normal first group mode.

(8) The compute by clause can use multiple statistical functions, which do not affect each other.

(9) The compute by clause can not contain the by clause, but only uses the compute clause to group the preceding information, instead only collects statistics on all information.

Comparison between COMPUTE and GROUP
The differences between COMPUTE and group by are summarized as follows:
Group by generates 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 generates multiple result sets. A type of result set contains the detailed rows of each group, including the expressions in the selection list. Another type of result set contains sub-aggregation of the group, or SELECT statement
. The selection list can contain expressions other than grouping based on columns or aggregate functions. The aggregate function is specified in the COMPUTE clause, rather than in the selection list.
The following query uses the group by and Aggregate functions. This query returns a result set with one row in each GROUP. This row contains the aggregate subtotal of this GROUP:
USE pubs
SELECT type, SUM (price), SUM (advance)
FROM titles
Group by type

The Data Types of ntext, text, or image cannot be included in the COMPUTE or compute by clause.