Aggregate Functions

1. AVERAGE()
The AVG() function returns the average value of a numeric column. Syntax
SELECT AVG(column_name) FROM table_name;
SELECT AVG(OrderPrice) AS OrderAverage FROM Orders
Order Average = 950
2. MIN()
The MIN() function returns the smallest value of the selected column. Syntax
SELECT MIN(column_name) FROM table_name
SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders
SmallestOrderPrice = 100
3. MAX()
The MAX() function returns the largest value of the selected column. Syntax
SELECT MAX(column_name) FROM table_name
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders
LargestOrderPrice = 2000
4. COUNT()
The COUNT() function returns the number of rows that matches a specified criteria. The
COUNT(column_name) function returns the number of values (NULL values will not be counted) of the
specified column:
SELECT COUNT(column_name) FROM table_name
SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Nilsen'
Custiomer Nilsen = 2
The COUNT(*) function returns the number of records in a table
SELECT COUNT(*) FROM table_name
SELECT COUNT(*) AS NumberOfOrders FROM Orders
NumberOfOrders = 6




5. SUM()
The SUM() function returns the total sum of a numeric column. Syntax
SELECT SUM(column_name) FROM table_name
SELECT SUM(OrderPrice) AS OrderTotal FROM Orders
OrderTotal = 5700
6. GREATEST()
The greatest function returns the greatest value in a list of expressions. The syntax for the greatest function
is:
greatest( expr1, expr2, ... expr_n )
expr1, expr2, . expr_n are expressions that are evaluated by the greatest function. If the datatypes of the
expressions are different, all expressions will be converted to whatever datatype expr1 is. If the comparison
is based on a character comparison, one character is considered greater than another if it has a higher
character set value. For example:
greatest(2, 5, 12, 3) would return 12
greatest('2', '5', '12', '3') would return '5'
greatest('apples', 'oranges', 'bananas') would return 'oranges'
greatest('apples', 'applis', 'applas') would return 'applis'
7. LEAST()
The least function returns the smallest value in a list of expressions. The syntax for the least function is:
least( expr1, expr2, ... expr_n )
expr1, expr2, . expr_n are expressions that are evaluated by the least function. If the datatypes of the
expressions are different, all expressions will be converted to whatever datatype expr1 is. If the comparison
is based on a character comparison, one character is considered smaller than another if it has a lower
character set value.
Note: Having a NULL value in one of the expressions will return NULL as the least value.
For example:
least(2, 5, 12, 3) would return 2
least('2', '5', '12', '3') would return '12'
least('apples', 'oranges', 'bananas') would return 'apples'
least('apples', 'applis', 'applas') would return 'applas'
least('apples', 'applis', 'applas', null) would return NULL