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