GROUP BY & Aggregate Functions Explained (the WHERE vs HAVING mistake almost everyone makes)

If you've written more than a handful of SQL queries, you've used GROUP BY. But there's a difference between writing a GROUP BY that happens to run and actually understanding what it's doing — and that gap is exactly where a lot of junior developers get tripped up in interviews and in code review.

This post walks through GROUP BY and the five core aggregate functions from the ground up, then covers the mistake that catches almost everyone at least once: mixing up WHERE and HAVING.

The problem GROUP BY solves

Say you've got an orders table with one row per order:

order_id | region | product_category | sales | order_date
---------+--------+-------------------+-------+------------
1        | West   | Electronics       | 120   | 2026-01-03
2        | East   | Furniture         | 340   | 2026-01-04
3        | West   | Electronics       | 90    | 2026-01-05
...

If this table has 40,000 rows, nobody wants to read all 40,000 of them. What people actually ask for is a summary: total sales per region, average order value per customer, number of orders per day. That's the entire point of GROUP BY — it collapses many rows into one row per group, so an aggregate function has something meaningful to compute.

The five aggregate functions you'll use constantly

COUNT(*)      -- how many rows
SUM(amount)   -- total of a column
AVG(amount)   -- mean of a column
MIN(amount)   -- smallest value
MAX(amount)   -- largest value

Each of these takes many rows as input and returns a single value. Used without GROUP BY, an aggregate function collapses the entire result set down to one row:

SELECT COUNT(*) AS total_orders
FROM orders;

That returns exactly one row — the total order count across the whole table. Add GROUP BY, and instead of collapsing everything into one row, SQL collapses the rows within each group into one row per group.

Writing your first GROUP BY

SELECT region,
       SUM(sales) AS total_sales
FROM orders
GROUP BY region;

Here's what happens under the hood: SQL scans the table and buckets every row by its region value. Then, separately for each bucket, it runs SUM(sales). The result has one row per distinct region — not one row per order.

This is the mental model that matters: GROUP BY defines the buckets, aggregate functions summarize what's inside each bucket.

WHERE vs HAVING — the mistake that catches everyone

This is the part that trips up almost every junior developer at least once, so it's worth slowing down for.

SELECT region,
       SUM(sales) AS total_sales
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY region
HAVING SUM(sales) > 10000;

• WHERE filters individual rows, and it runs before grouping happens. In the query above, any order placed before January 1st is thrown out before the grouping and summing even start.
• HAVING filters entire groups, and it runs after aggregation. Here, it drops any region whose total sales didn't clear $10,000 — but only after SUM(sales) has already been calculated for every region.

Here's the mistake: try writing WHERE SUM(sales) > 10000 instead of using HAVING, and most databases — including Snowflake and Postgres — will throw an error. The reason is order of operations: at the point WHERE is evaluated, grouping hasn't happened yet, so there's no per-group SUM(sales) for WHERE to check against. The aggregate simply doesn't exist yet at that stage of query execution. HAVING exists specifically because SQL needed a filter clause that runs after aggregation.

A useful rule of thumb: if the condition references a raw column (order_date, status, customer_id), it goes in WHERE. If it references an aggregate function (SUM(...), COUNT(...), AVG(...)), it goes in HAVING.

Grouping by more than one column

You're not limited to grouping by a single column:

SELECT region,
       product_category,
       COUNT(*) AS orders,
       SUM(sales) AS total_sales
FROM orders
GROUP BY region, product_category;

This groups by every unique combination of region and product_category. "West / Electronics" and "West / Furniture" become two separate rows in the output, even though they share the same region — because the grouping key is the pair of columns together, not each column independently.

The gotcha that breaks queries

This is the error message you'll eventually run into if you haven't already:

-- Will not run in Snowflake, Postgres, or most standards-compliant databases
SELECT region, customer_name, SUM(sales)
FROM orders
GROUP BY region;

The problem: customer_name appears in the SELECT list, but it's neither wrapped in an aggregate function nor included in GROUP BY. Once you group by region, each output row represents many orders from potentially many different customers — so which customer_name should SQL display for that row? There's no single correct answer, so Snowflake and Postgres refuse to run the query at all.

The fix is either to aggregate the column or add it to the GROUP BY list:

-- Aggregate it
SELECT region, COUNT(DISTINCT customer_name) AS unique_customers, SUM(sales)
FROM orders
GROUP BY region;

-- Or group by it
SELECT region, customer_name, SUM(sales)
FROM orders
GROUP BY region, customer_name;

One thing worth knowing if you work across different database engines: MySQL has historically been more lenient here and will sometimes let a non-aggregated, non-grouped column through, silently picking an arbitrary value for it per group. That's rarely what you actually want, and it's part of why Snowflake and Postgres's stricter behavior is the safer default to reason about — it forces you to be explicit rather than getting a query that runs but returns a value you didn't intend.

Order of operations, all in one place

It helps to know the actual evaluation order of a query with all these clauses, since it's not the same as the order you type them in:

1. FROM — pick the source table(s)
2. WHERE — filter individual rows
3. GROUP BY — bucket the remaining rows into groups
4. Aggregate functions run — one value computed per group
5. HAVING — filter out entire groups based on the aggregated values
6. SELECT — choose which columns/expressions to return
7. ORDER BY — sort the final result

Keeping this order in your head is what makes WHERE vs HAVING make sense: WHERE runs at step 2, before groups exist, and HAVING runs at step 5, after they do.

A few practice queries to try

If you've got access to any table with a few thousand rows and at least one categorical column, try these:

1. Count of rows per category
2. Average of some numeric column per category
3. Categories where the average exceeds some threshold (this one requires HAVING)
4. Grouping by two columns instead of one
5. Combining a WHERE filter and a HAVING filter in the same query

Working through all five is usually enough to make the WHERE/HAVING distinction permanent.

Wrapping up

GROUP BY and aggregate functions are some of the most-used tools in SQL, and they're also one of the most common sources of small, confusing errors for people early in their SQL journey. The fix isn't memorizing syntax — it's holding onto the mental model: GROUP BY defines the buckets, aggregate functions summarize what's inside them, WHERE filters before that happens, and HAVING filters after.

If this was useful, I post SQL, Python, and Snowflake breakdowns like this regularly on Instagram @techqueen.codes — feel free to follow along there for the shorter, visual version of posts like this one.