SQL GROUP BY and HAVING: Full Guide (2026)

Quick Answer: What Do GROUP BY and HAVING Do?

The Customer Report With the Wrong Name

Older MySQL configurations, with a relaxed sql_mode, allow a query like SELECT customer_id, name, COUNT(*) FROM orders GROUP BY customer_id to run without complaint — even though name isn't in GROUP BY and isn't wrapped in an aggregate function. The database silently picks some value of name from one of the rows in each group, with no guarantee it's the right one if a customer's name was ever updated mid-dataset. The query returns real-looking output: a customer ID, a name, and a count. It's just that the name attached to the count might not actually belong together.

Modern PostgreSQL, BigQuery, and MySQL with standard settings reject this outright with an error — which is the better outcome, because a loud error is far easier to catch than a silently wrong report. Understanding exactly why the rule exists is the fastest way to stop fighting it.

Anatomy of GROUP BY

GROUP BY pairs almost always with at least one aggregate function — otherwise there's nothing to compute once rows are collapsed.

-- how many orders does each customer have?
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

Reading this: every row in orders gets bucketed by its customer_id, one bucket per unique customer. COUNT(*) then counts how many rows landed in each bucket. The result isn't the original rows anymore — it's one summarized row per customer, exactly what "aggregation" means.

The GROUP BY Column Rule

This is the rule behind the hook at the top of this article: every column in the SELECT list must either be in GROUP BY, or be wrapped in an aggregate function. There is no third option in standard SQL.

-- INVALID on Postgres/BigQuery/standard MySQL: name is neither grouped nor aggregated
SELECT customer_id, name, COUNT(*)
FROM orders
GROUP BY customer_id;

-- FIX 1: add name to GROUP BY
SELECT customer_id, name, COUNT(*)
FROM orders
GROUP BY customer_id, name;

-- FIX 2: aggregate it instead, if multiple names per group is expected
SELECT customer_id, MAX(name) AS name, COUNT(*)
FROM orders
GROUP BY customer_id;

The reasoning: once rows collapse into a group, a non-aggregated column could hold several different values within that same group — the database has no defensible rule for which one to show, so standard SQL simply forbids it. Adding the column to GROUP BY is correct when it's genuinely one-to-one with the grouping key (a customer really only has one name); wrapping it in an aggregate is correct when you're deliberately picking one value from several.

Grouping by Multiple Columns

GROUP BY isn't limited to one column — listing several creates one group per unique combination of those columns, not one group per column.

-- total revenue per country, per product category
SELECT country, product_category, SUM(order_total) AS revenue
FROM orders
GROUP BY country, product_category;

If there are 5 countries and 4 product categories, this can produce up to 20 groups — one for every country/category pair that actually appears in the data. This is the standard pattern for cross-tabulated reports: "revenue by region and category" is always a multi-column GROUP BY under the hood.

HAVING vs WHERE: Filtering Groups, Not Rows

WHERE and HAVING both filter, but they operate at different stages — this is the single most important distinction in this article.

-- WHERE removes individual rows before grouping
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE status != 'cancelled'
GROUP BY customer_id;

-- HAVING removes entire groups after aggregation
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

-- both together: exclude cancelled orders, then keep only customers with 5+ remaining
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE status != 'cancelled'
GROUP BY customer_id
HAVING COUNT(*) > 5;

A practical habit: filter with WHERE whenever possible, since it discards rows before the (more expensive) grouping and aggregation work happens. Reach for HAVING only for conditions that genuinely depend on the aggregate itself, like "customers with more than 5 orders" — there's no way to know that count without aggregating first.

Putting It All Together: Clause Order

A full query with every clause covered in this cluster so far follows a fixed written order, which differs from the order the database actually evaluates it in.

This mismatch between written order and evaluation order is exactly why WHERE can't see a SELECT alias (SELECT hasn't run yet) and why WHERE can't use an aggregate (GROUP BY hasn't run yet). Once this table clicks, most of the "why doesn't this work" questions in SQL answer themselves.

COUNT(*) vs COUNT(column): The NULL Difference

These look interchangeable and are not. COUNT(*) counts every row in a group, full stop. COUNT(column) counts only the rows where that specific column is not NULL.

-- total orders per customer, including ones with a missing discount_code
SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id;

-- only orders where a discount_code was actually used
SELECT customer_id, COUNT(discount_code) AS orders_with_discount
FROM orders
GROUP BY customer_id;

If a customer placed 10 orders and only 3 used a discount code, COUNT(*) returns 10 and COUNT(discount_code) returns 3 — same table, same group, genuinely different numbers, both correct for what they're asking. Picking the wrong one is a quiet way to under- or over-report a metric without any error to flag it.

Mistakes That Give Away a Beginner

• Selecting a non-grouped, non-aggregated column — the exact trap from this article's opening. Either add it to GROUP BY or wrap it in an aggregate function.
• Trying to filter an aggregate with WHERE instead of HAVING. COUNT, SUM, and friends don't exist yet at the point WHERE runs.
• Using HAVING for a condition that doesn't need aggregation. If a filter only touches raw columns, WHERE is both correct and faster, since it discards rows before the grouping work happens.
• Confusing COUNT(*) and COUNT(column) on a table where the column has NULLs, silently under- or over-counting.
• Forgetting that GROUP BY with multiple columns groups by the combination, not producing one group per column independently.

Practice: Write These Three Queries Yourself

Using the same orders table (order_id, customer_id, product_name, price, quantity, order_date, status), try writing these:

Frequently Asked Questions

Conclusion: Group Deliberately, Filter at the Right Stage

GROUP BY turns individual rows into summarized groups, and the one rule to internalize is that every SELECT column needs to be either a grouping key or wrapped in an aggregate — there's no ambiguous middle ground in correct SQL. Filter rows with WHERE before grouping whenever you can; reach for HAVING only when the condition genuinely depends on an aggregate value. And remember COUNT(*) and COUNT(column) are not interchangeable the moment NULLs are in play.

Practice the three queries above against a real table, then move to the next article in this cluster, which covers JOINs — how tables get combined before you're even aggregating across them.