SQL Server series: aggregate functions

Source: Internet
Author: User

1. SUM

Sum is a summation function that returns the sum of the specified column values. SUM can only be used for numeric columns. where Null values are ignored.

Grammar:

SUM []  expression)   over[] order_by_ clause)  

Example:

SELECT SUM ([unitsinstock]from[dbo]. [Product]
SELECT SUM (DISTINCT([unitsinstock]from[dbo].  [Product]

Sum can be used with group by to calculate the sum of each grouping.

SELECT [CategoryID],SUM([unitsinstock])  from[dbo]. [Product] GROUP  by [CategoryID]

2. AVG

  Returns the average of the values in the group. where Null values are ignored.

Grammar:

AVG []  expression)   over[] order_by_ clause)  

Example:

SELECT AVG ([unitsinstock]from[dbo]. [Product]
SELECT [CategoryID],AVG([unitsinstock])  from[dbo]. [Product] GROUP  by [CategoryID]

3. MAX

Returns the maximum value in an expression.

Grammar:

MAX []  expression)   over[] order_by_ clause)  

Example:

SELECT MAX ([unitsinstock]from[dbo]. [Product]
SELECT [CategoryID],MAX([unitsinstock])  from[dbo]. [Product] GROUP  by [CategoryID]

4. MIN

Returns the minimum value in an expression.

Grammar:

MIN ([All | DISTINCT] expression) over   ([Partition_by_clause] order_by_clause)  

Example:

SELECT MIN ([UnitsInStock]) from [dbo]. [Product]
SELECT [Categoryid],min ([UnitsInStock]) from [dbo].[ Product]group by [CategoryID]

6. COUNT

Returns the number of items in a group. COUNT is similar to the COUNT_BIG function. The only difference between the two functions is their return value. COUNT always returns the int data type value. Count_big always returns the bigint data type value.

Grammar:

COUNT []| * })     over[] order_by_clause)

Example:

 select  count  ([ unitsinstock  ] ) from  [ dbo  ] . [  ]
SELECT [CategoryID],COUNT([unitsinstock])  from[dbo]. [Product] GROUP  by [CategoryID]

SQL Server series: aggregate functions

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.