Introduction
Aggregate functions are a fundamental part of SQL for data analysis and reporting. They allow you to summarize data efficiently. Instead of processing each row individually, an aggregate function processes a group of rows and returns a summary value.
Common Aggregate Functions
Here are some of the most commonly used SQL aggregate functions:
COUNT()
Returns the number of rows that match a specified criterion.
COUNT(*): Counts all rows in a table or group.COUNT(column_name): Counts the number of non-NULL values in a specified column.
Syntax:
COUNT(expression)Example:
SELECT COUNT(*) FROM Customers;SUM()
Returns the total sum of numeric values in a specified column.
Syntax:
SUM(expression)Example:
SELECT SUM(OrderTotal) FROM Orders;AVG()
Returns the average value of numeric values in a specified column.
Syntax:
AVG(expression)Example:
SELECT AVG(Price) FROM Products;MIN()
Returns the smallest value in a column.
Syntax:
MIN(expression)Example:
SELECT MIN(OrderDate) FROM Orders;MAX()
Returns the largest value in a column.
Syntax:
MAX(expression)Example:
SELECT MAX(Salary) FROM Employees;Using with GROUP BY
Aggregate functions are most powerful when combined with the GROUP BY clause. This allows you to group rows that have the same values in one or more columns and then apply an aggregate function to each group.
Example: Count orders per customer
Let's say you have an Orders table with CustomerID and OrderID columns.
| CustomerID | OrderID |
|---|---|
| 101 | 1001 |
| 102 | 1002 |
| 101 | 1003 |
| 103 | 1004 |
| 102 | 1005 |
SELECT CustomerID, COUNT(OrderID) AS NumberOfOrders
FROM Orders
GROUP BY CustomerID;Result:
| CustomerID | NumberOfOrders |
|---|---|
| 101 | 2 |
| 102 | 2 |
| 103 | 1 |
Filtering Groups with HAVING
While the WHERE clause filters individual rows *before* they are aggregated, the HAVING clause filters groups *after* the aggregation has been performed.
Example: Customers with more than 1 order
SELECT CustomerID, COUNT(OrderID) AS NumberOfOrders
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 1;Result:
| CustomerID | NumberOfOrders |
|---|---|
| 101 | 2 |
| 102 | 2 |
Advanced Usage
Aggregate functions can also be used in conjunction with DISTINCT to count only unique values, or combined in expressions for more complex calculations.
Example: Count distinct cities
SELECT COUNT(DISTINCT City) FROM Customers;Example: Average order total per customer, only for customers with more than $500 in total orders
SELECT CustomerID, AVG(OrderTotal) AS AverageOrderValue
FROM Orders
GROUP BY CustomerID
HAVING SUM(OrderTotal) > 500;Understanding and utilizing aggregate functions is crucial for extracting meaningful insights from your SQL databases.