Aggregate Functions and GROUP BY

Site Admin · 11 Sep 2026 · 6 views

Aggregate Functions and GROUP BY

Summarizing Data

Sometimes you do not want every row, but a summary: the average price, the number of orders, the biggest sale. Aggregate functions compute a single value from a group of rows. The big five are COUNT, SUM, AVG, MIN, and MAX.

SELECT COUNT(*) FROM orders;
SELECT SUM(total) FROM orders;
SELECT AVG(total) FROM orders;
SELECT MIN(total), MAX(total) FROM orders;

COUNT(*) counts all rows, while COUNT(column) counts non-NULL values of that column, so they can differ when NULLs are present.

Grouping with GROUP BY

GROUP BY splits rows into groups, and each aggregate runs separately within its group. Grouping orders by customer_id gives one summary per customer.

SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS spent
FROM orders
GROUP BY customer_id;

Every column in the SELECT that is not inside an aggregate must appear in the GROUP BY clause. This rule stops nonsensical results when MySQL has several values to choose from.

Filtering Groups with HAVING

WHERE filters rows before grouping, and HAVING filters groups after. To find only customers with more than five orders, HAVING is the tool.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING order_count > 5;

Notice that HAVING can reference the alias order_count, which WHERE cannot do because the alias is only defined later in the statement's execution.

Putting It Together

Aggregates compose with the other clauses in a fixed order: SELECT with aggregates, FROM the source, WHERE to filter rows, GROUP BY to group, HAVING to filter groups, and ORDER BY to sort. Reciting that order from memory makes reading any query far easier.