What are the aggregate functions in SQL Server?-SQL-php.cn

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.

What are the aggregate functions in SQL Server?

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

Copy after login

2, COUNT

Returns the number of items in the specified group.

For example:

select count(prd_no) from sales

Copy after login

3, MAX

## Returns the maximum value of the specified data.

For example:

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

Copy after login

4, MIN

## Returns the minimum value of the specified data.

For example:

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

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

13, VAR

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

For example:

select var(prd_no) from sales

Copy after login

14, VARP

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

For example:

select varp(prd_no) from sales

Copy after login

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!