What are the SQL aggregate functions?

SQL aggregate functions include: 1. AVG function; 2. COUNT function; 3. MAX function; 4. MIN function; 5. SUM function; 6. GROUPING function; 7. CHECKSUM function; 8. STDEV function ; 9. STDEVP function; 10. VAR function; 11. VARP function, etc.

#The aggregate functions in SQL include: the AVG function that specifies the average value in the group, the COUNT function that returns the number of items in the group, and the maximum value of the data that is returned. MAX function, SUM function that returns the sum of data, etc.

The aggregate function is a function that performs calculations on a set of values ​​and returns a single value. It is often used with the GROUP BY clause of the SELECT statement, in SQL SERVER What specific aggregate functions are there? Let’s take a look:

1. AVG returns the average value in the specified group, and null values ​​are ignored.

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

2. COUNT Returns the number of items in the specified group.

Example: select count(prd_no) from sales

3. MAX Returns the maximum value of the specified data.

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

4. MIN Returns the minimum value of the specified data.

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

5. SUM returns the sum of the specified data and can only be used for numeric columns. NULL values ​​are ignored.

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

6. COUNT_BIG returns the number of items in the specified group. The difference from the COUNT function is that COUNT_BIG returns a bigint value, while COUNT What is returned is an int value.

Example: select count_big(prd_no) from sales

7. GROUPING generates an additional column. When a row is added using the CUBE or ROLLUP operator, the output value is 1. When the added row When it is not generated by CUBE or ROLLUP, the output value is 0.

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

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

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.

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

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.

Example: select stdev(prd_no) from sales

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

Example: select stdevp(prd_no) from sales

13. VAR returns the statistical variance of all values ​​in the given expression.

Example: select var(prd_no) from sales

14. VARP returns the filled statistical variance of all values ​​in the given expression.

Example: select varp(prd_no) from sales

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