SQL Server compute by clause Usage Analysis

Use the compute by clause and the ROW statistics function (count, sum, Max, AVG, Min, etc.) to calculate columns with identical results in sorting, the statistical value is displayed as an additional row as the query result,

Syntax: Compute AVG | count | max | min | sum by expression

1. Example

For example, a data table named "person" has three fields: name, age, and country.

Select name, country, age from person where ?? Order by a compute sum (AGE), max (AGE), min (AGE) by country

This statement groups the data according to country during query, and then displays the details and statistics of each group. The result may be as follows:

Name Country age

James China 16
Li Si China 21
Wang Wu, China 24

Sum Max min

61 24 16

======================================

Name Country age

Tyson USA 20
Bush USA 24
Gates, USA 25

Sum Max min

69 25 20

========================================================== =====

Name Country age

Prostitutes Japan 12
Complete Japanese shard 14
Pig, Japanese 15

Sum Max min

41 15 12

We can easily see that she actually contains three subqueries in a query result (groups are divided according to country ), each subquery contains two subqueries (details and statistics)

2. compute by clause rules:

(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.