SQL Server compute by clause usage example

1. Related Concepts

If you need to save the query results and display the statistical results under the query results, you can use the compute by clause.

The compute clause is used to generate a total and list it as an additional summary at the end of the current result set. When used together with the by clause, the compute clause generates a control interruption and Classification summary in the result set. You can specify compute by and compute in the same query.

The syntax format is as follows:

[Compute

{

{AVG | count | max | min | STDev | stdevp | Var | varp | sum (expression )}[,... N]

[By expressin [,...]

}]

Aggregate functions used in the compute clause

Note: The field alias cannot be used for Aggregate functions.

 

Ii. instance description

1. Preparations

If object_id ('student ', 'table') is not nullddrop table studentgocreate table student (ID int identity () not null, sex varchar (4) not null, sclass varchar (10), score int, constraint zhujian primary key (ID), constraint scorecheck check (score> 0 and score <= 10) insert into student values ('male ', 1, 2) insert into student values ('female ', 2, 4) insert into student values ('female', 2, 6) insert into student values ('female ', 1, 2) insert into student values ('male',) insert into student values ('male)

2. Group by clause

-- Group words select sex, sclass, score, sum (score) as total score from studentgroup by sex, sclass, scoreorder by sex

Result 3: Use the compute by clause.

-- Comlpute by statement select sex, sclass, score, sum (score) as total score from studentgroup by sex, sclass, scoreorder by sexcompute sum (score), AVG (score ), max (score), min (score) by sex

The result is as follows:

Finished.