MySQL Aggregate Functions

What Are Aggregate Functions?

An aggregate function performs a calculation on a set of rows and returns a single value, rather than one value per row. MySQL provides several built-in aggregate functions, including COUNT(), SUM(), AVG(), MIN(), and MAX(), all of which are commonly used to summarize data such as sales totals, average prices, or the number of orders placed.

• COUNT() — counts the number of rows or non-NULL values
• SUM() — adds up all the numeric values in a column
• AVG() — calculates the mean of the numeric values in a column
• MIN() — finds the smallest value in a column
• MAX() — finds the largest value in a column

Aggregating an Entire Table

When used without GROUP BY, an aggregate function collapses the entire result set of a query down into a single row, summarizing every row that matched the WHERE clause.

SELECT COUNT(*) AS TotalProducts
FROM Products;

Aggregating Per Group With GROUP BY

Aggregate functions become far more powerful when combined with GROUP BY, which splits the rows into buckets — one bucket per distinct value in the grouping column — and then applies the aggregate function separately to each bucket. This is how you calculate, for example, the total number of orders placed by each individual customer.

SELECT CustomerID, COUNT(OrderID) AS NumberOfOrders
FROM Orders
GROUP BY CustomerID;

Filtering Groups With HAVING

WHERE filters individual rows before grouping happens, but it cannot reference an aggregate result. To filter based on an aggregated value — such as only showing customers with more than 5 orders — you need the HAVING clause, which runs after grouping and aggregation are complete.

SELECT CustomerID, COUNT(OrderID) AS NumberOfOrders
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 5;