MySQL Aggregate Functions: COUNT, SUM, AVG, GROUP BY & HAVING Explained

Raw rows rarely answer the question you actually care about. You don't want a list of every order ever placed, you want to know how many orders came in last month, which customer spent the most, or how many distinct cities you shipped to. That's what MySQL aggregate functions are for: they collapse a pile of rows into the single number or short list that actually matters.

This guide walks through COUNT(), MIN(), MAX(), SUM(), AVG(), DISTINCT, GROUP BY, and HAVING using one running example, so you can see how each piece fits into the next.

Cr Image: ChatGPT.

The table we'll use

Picture a simple orders table with columns id, customer, region, amount, and order_date. Every example below builds on this shape, so swap in your own table and column names as you go.

Counting rows with COUNT()

COUNT() is the workhorse of summary queries. Ask "how many?" and COUNT() is almost always the answer.

SELECT COUNT(*) FROM orders;

That counts every row, no exceptions. Add a WHERE clause to count only the rows you care about:

SELECT COUNT(*) FROM orders WHERE amount > 500;

Here's the detail that trips people up: COUNT(*) and COUNT(column_name) aren't the same thing. COUNT(*) counts rows regardless of content. COUNT(column_name) only counts rows where that specific column isn't NULL. So if your region column has some blank entries, COUNT(*) and COUNT(region) will give you different numbers, and that gap tells you exactly how many rows are missing a region.

SELECT COUNT(*) AS total_rows, COUNT(region) AS rows_with_region
FROM orders;

Finding extremes with MIN() and MAX()

MIN() and MAX() pull out the smallest and largest value in a column. Numbers, dates, and strings all work, since MIN/MAX in MySQL just apply normal sort order to whatever type you give them.

SELECT MIN(order_date) AS earliest, MAX(order_date) AS latest,
       MIN(amount) AS cheapest, MAX(amount) AS priciest
FROM orders;

One catch worth remembering: MIN() and MAX() tell you the value, not which row it came from. If you also want to know which customer placed the biggest order, MIN/MAX alone won't get you there. You'll need a join, a subquery, or a session variable trick, since MySQL won't let you reference an aggregate function directly inside a WHERE clause.

Totals and averages with SUM() and AVG()

SUM() adds up a numeric column. AVG() gives you the mean. Both ignore NULL values automatically, so a handful of missing entries won't quietly drag your average down to zero.

SELECT SUM(amount) AS total_revenue, AVG(amount) AS average_order_value
FROM orders;

Keep in mind that SUM() and AVG() only work on numeric data. You can't average a column of names, and MySQL will complain if you try to feed it strings or dates directly, though you can convert values first if there's a genuine numeric meaning hiding underneath.

Getting unique values with DISTINCT

Sometimes you don't want a count or a total, you just want to know which values actually show up. That's DISTINCT.

SELECT DISTINCT region FROM orders ORDER BY region;

To count how many unique values exist rather than list them, pair DISTINCT with COUNT():

SELECT COUNT(DISTINCT region) FROM orders;

This combination answers a specific and common question: not "how many orders do we have" but "how many different regions do we actually sell into."

Breaking a summary into groups with GROUP BY

Every example so far has summarized the entire table into one row. GROUP BY splits that single summary into per-category summaries instead. Add a GROUP BY clause and MySQL buckets your rows by whatever column you name, then runs the aggregate function separately on each bucket.

SELECT region, COUNT(*) AS order_count, SUM(amount) AS total_sales
FROM orders
GROUP BY region;

That single query now tells you order volume and revenue broken out by region, which is a far more useful report than one grand total. You can group by more than one column too, which nests the groupings:

SELECT region, customer, COUNT(*) AS order_count
FROM orders
GROUP BY region, customer;

The mistake almost everyone makes with GROUP BY

Once you're grouping rows, you can only select the grouped columns themselves or values built from aggregate functions. Adding an unrelated column to the SELECT list without grouping by it or wrapping it in an aggregate produces results that look plausible but are quietly wrong, since MySQL has no defined way to pick which row's value to show.

Say you want each region's biggest order along with the date it happened. Tacking order_date onto a GROUP BY region query won't reliably give you the date that matches the MAX(amount) for that region. The two values get pulled from whatever row MySQL happens to land on, not necessarily the same row. The fix is a join against a subquery that first isolates the maximum value per group, then matches back to the full row.

Filtering groups with HAVING

WHERE filters individual rows before grouping happens. It has no idea what an aggregate value will end up being, because that value doesn't exist yet at the point WHERE runs. If you want to filter based on a computed total or count, you need HAVING instead.

SELECT region, COUNT(*) AS order_count
FROM orders
GROUP BY region
HAVING order_count > 10;

Think of HAVING as WHERE's counterpart for groups rather than rows. WHERE narrows down what goes into each group, HAVING narrows down which finished groups make it into your results. This is exactly how you'd find "customers who placed more than 5 orders" or "regions with total sales under $10,000," queries that are impossible to write correctly with WHERE alone.

How NULL values behave in summary queries

NULL handling catches a lot of people off guard. Most aggregate functions, COUNT(column), SUM(), AVG(), MIN(), and MAX(), skip NULL values entirely rather than treating them as zero. That's usually what you want: a missing value shouldn't drag down an average or masquerade as a real minimum.

But when a group has nothing but NULLs to summarize, AVG(), MIN(), and MAX() return NULL, while SUM() and COUNT() return 0. If a blank NULL in your report looks confusing, wrap the aggregate in IFNULL() to substitute a friendlier value:

SELECT region, IFNULL(AVG(amount), 0) AS average_order
FROM orders
GROUP BY region;

Putting it all together

These pieces are meant to be combined, not used in isolation. A realistic reporting query might use SUM() and COUNT() together, group by more than one column, filter rows with WHERE before grouping, and filter finished groups with HAVING afterward:

SELECT region, customer, COUNT(*) AS orders_placed, SUM(amount) AS total_spent
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY region, customer
HAVING total_spent > 1000
ORDER BY total_spent DESC;

That one query answers a genuinely useful business question: which customers, in which regions, have spent more than $1,000 since the start of the year. Once WHERE, GROUP BY, and HAVING click into place as three separate stages rather than three interchangeable filters, aggregate queries stop feeling like guesswork and start feeling like plain arithmetic.

Share :