Use of SQL compute

A disadvantage of the Group by clause is that the returned result set only contains the total data, but does not have the original detailed records. If you want
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, compute
The clause generates a summary of control interruptions and categories in the result set.

The following SELECT statement uses the simple compute clause to generate the price and advance in the titles table
Total sum:

UsePubs
SelectType, price, advance
FromTitles
Order ByType
Compute Sum(Price ),Sum(Advance)

 

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

 UsePubs

Select Type, price, advance
From Titles
Order   By Type
Compute   Sum (Price ), Sum (Advance) By Type

 

This
The result of the SELECT statement is 12.
Result sets are returned. 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 compute
The subtotal of the two sum functions in the 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.
Clause, and the sequence of the column must be the same as that of the Start item. (To put it bluntly, the columns in the compute by clause must be all of the 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
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 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.
Use the following query
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

Description in
The compute or compute by clause cannot contain ntext, text, or image data types.