SQL - Aggregate functions

An aggregate function is a function that performs a calculation on a set of values, and returns a single value.

Aggregate functions are often used with the GROUP BY clause of the SELECT statement. The GROUP BY clause splits the result-set into groups of values and the aggregate function can be used to return a single value for each group.

The most commonly used SQL aggregate functions are:

• MIN() - returns the smallest value within the selected column
• MAX() - returns the largest value within the selected column
• COUNT() - returns the number of rows in a set
• SUM() - returns the total sum of a numerical column
• AVG() - returns the average value of a numerical column

SELECT MIN(Price)
FROM Products;

SELECT MAX(Price)
FROM Products;

Set Column Name (Alias):

When you use MIN() or MAX(), the returned column will not have a descriptive name. To give the column a descriptive name, use the AS keyword

SELECT MIN(Price) AS SmallestPrice
FROM Products;

SELECT MIN(Price) AS SmallestPrice, CategoryID
FROM Products
GROUP BY CategoryID;

SELECT COUNT(*)
FROM Products;

Note: If you specify a column name instead of (*), NULL values will not be counted.

SELECT OrderID, SUM(Quantity) AS [Total Quantity]
FROM OrderDetails
GROUP BY OrderID;

SELECT AVG(Price) AS AveragePrice, CategoryID
FROM Products
GROUP BY CategoryID;