What are the aggregate functions in SQL Server?

Aggregation functions include: 1. AVG, returns the average value; 2. COUNT, returns the number of items in the specified group; 3. MAX, returns the maximum value of the specified data; 4. MIN, returns the minimum value of the specified data Value; 5. SUM, returns the sum of specified data; 6. VAR, returns the statistical variance of all values ​​in a given expression; 7. VARP, etc.

The operating environment of this tutorial: windows7 system, SQL Server 2019 version, Dell G3 computer

sql server Aggregation functions are:

1, AVG

Returns the average value in the specified group, and null values ​​are ignored.

For example:

select  prd_no,avg(qty) from sales group by prd_no

2, COUNT

Returns the number of items in the specified group.

For example:

select  count(prd_no) from sales

3, MAX

## Returns the maximum value of the specified data.

For example:

select  prd_no,max(qty) from sales group by prd_no

4, MIN

## Returns the minimum value of the specified data.

For example:

select  prd_no,min(qty) from sales group by prd_no

5, SUM

Returns the sum of the specified data, can only be used for numeric columns, empty Value is ignored.

For example:

select  prd_no,sum(qty) from sales group by prd_no

6, COUNT_BIG

Returns the number of items in the specified group, which is different from the COUNT function. COUNT_BIG returns a bigint value, while COUNT returns an int value.

For example:

select  count_big(prd_no) from sales

7, GROUPING

Generates an additional column when added with the CUBE or ROLLUP operator When the row is added, the output value is 1. When the added row is not generated by CUBE or ROLLUP, the output value is 0

For example:

select  prd_no,sum(qty),grouping(prd_no) from sales group by prd_no with rollup

8, BINARY_CHECKSUM

Returns a binary check value calculated on a row in a table or a list of expressions, used to detect changes to rows in the table.

For example:

select  prd_no,binary_checksum(qty) from sales group by prd_no

9, CHECKSUM_AGG

Returns the check value of the specified data, and empty values ​​are ignored.

For example:

select  prd_no,checksum_agg(binary_checksum(*)) from sales group by prd_n

10, CHECKSUM

Returns the check value calculated on the row of the table or on the expression list , used to generate hash indexes.

11. STDEV

Returns the statistical standard deviation of all values ​​in the given expression.

select  stdev(prd_no) from sales

12, STDEVP

Returns the standard deviation of the fill statistics for all values ​​in the given expression.

For example:

select  stdevp(prd_no) from sales

13, VAR

Returns the statistical variance of all values ​​in the given expression.

For example:

select  var(prd_no) from sales

14, VARP

Returns the filled statistical variance of all values ​​in the given expression.

For example:

select  varp(prd_no) from sales

Related recommendations:

sql database technical articles

The above is the detailed content of What are the aggregate functions in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!