SQL Server series: aggregate functions

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