Various aggregate functions for SQL Server

Source: Internet
Author: User

An aggregate function is a function that performs a calculation on a set of values and returns a single value, which is often used in conjunction with the GROUP BY clause of a SELECT statement, and what aggregate functions are specifically in SQL SERVER? Let's take a look at: 1. AVG returns the average in the specified group, and the null value is ignored. Example: Select Prd_no,avg (qty) from the Sales group by Prd_no2. Count returns the number of items in the specified group. Example: SELECT count (prd_no) from Sales3. Max returns the maximum value for the specified data. Example: Select Prd_no,max (qty) from the Sales group by Prd_no4. Min Returns the minimum value for the specified data. Example: Select Prd_no,min (qty) from the Sales group by Prd_no5. The sum of the specified data is returned, and can only be used for numeric columns, and null values are ignored. Example: Select Prd_no,sum (qty) from the Sales group by Prd_no6. COUNT_BIG returns the number of items in the specified group, unlike the Count function, where COUNT_BIG returns the bigint value, and Count returns an int value. Example: Select COUNT_BIG (prd_no) from Sales7. GROUPING produces an additional column that, when added with the cube or rollup operator, outputs a value of 1. The output value is 0 when the added row is not generated by cube or Rollup. Example: Select Prd_no,sum (qty), Grouping (prd_no) from the sales group by Prd_no with Rollup8. Binary_checksum returns the binary checksum value computed for a list of rows or expressions in a table to detect changes in rows in a table. Example: Select Prd_no,binary_checksum (qty) from the Sales group by Prd_no9. Checksum_agg returns the checksum value for the specified data, and the null value is ignored. Example: Select Prd_no,checksum_agg (binary_checksum (*)) from the sales group by PRD_NO10. CHECKSUM returns a checksum value computed on the row of a table or on an expression list, used to generate a hash index. STDEV returns the statistics of all values in the given expressionStandard deviation. Example: Select Stdev (prd_no) from SALES12. STDEVP returns the fill statistic standard deviation for all values in the given expression. Example: Select STDEVP (prd_no) from Sales13. VAR returns the statistical variance of all values in the given expression. Example: Select VAR (prd_no) from SALES14. VARP returns the statistical variance of the fill for all values in the given expression. Example: Select VarP (prd_no) from sales

Various aggregate functions for SQL Server

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.