Shulou(Shulou.com)06/01 Report--

Editor to share with you what aggregate function in sql, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The aggregate functions in SQL are: the AVG function that specifies the average in the group, the COUNT function that returns the number of items in the group, the MAX function that returns the maximum value of the data, the SUM function that returns the sum of the data, and so on.

Aggregate functions are functions that perform calculations on a set of values and return a single value. It is often used with the GROUP BY clause of a SELECT statement. What are the specific aggregate functions in SQL SERVER? Let's take a look:

1. AVG returns the average value in the specified group, and the null value is 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, unlike the COUNT function, COUNT_BIG returns the Bigint value, while COUNT returns the int value.

Example: select count_big (prd_no) from sales

7. GROUPING produces an additional column, and when rows are added with CUBE or ROLLUP operators, the output value is 1. 0. When the added row is not generated by CUBE or ROLLUP, the output value is 0. 0.

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 list of rows or expressions in the table to detect changes to the 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 the null value is ignored.

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

10. CHECKSUM returns a check value calculated on the row of the table or on the list of expressions, which is used to generate a hash index.

11. STDEV returns the statistical standard deviation of all values in a given expression.

Example: select stdev (prd_no) from sales

12. STDEVP returns the padding statistical standard deviation of all values in a given expression.

Example: select stdevp (prd_no) from sales

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

Example: select var (prd_no) from sales

14. VARP returns the statistical variance of the padding of all values in a given expression.

Example: select varp (prd_no) from sales

These are all the contents of the article "what are the aggregate functions in sql?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.