SQL GROUP BY and HAVING Explained — WHERE vs HAVING with Real Examples

1. The Execution Order That Explains Everything

SQL is declarative, but engines still evaluate clauses in a fixed sequence. For a query with every clause present, it looks like this:

FROM / JOIN      -- build the working set of rows
WHERE            -- filter individual rows (before any grouping)
GROUP BY         -- collapse rows into groups
HAVING           -- filter groups (after aggregation)
SELECT           -- evaluate the output list, aliases, aggregate results
ORDER BY         -- sort the final result
LIMIT            -- cut the result set

Two consequences follow directly, and between them they explain almost every error people hit with these clauses:

• WHERE runs before grouping exists. There are no groups and no aggregates yet, so a condition like SUM(amount) > 200 has nothing to compute over. It is a hard error, not a warning: MySQL raises ERROR 1111 (42000): Invalid use of group function, PostgreSQL says aggregate functions are not allowed in WHERE.
• SELECT runs after GROUP BY. Once rows are collapsed, the only values still available per group are the grouping columns and aggregates over the rest. Selecting a bare, ungrouped column is ambiguous — and modern engines refuse it rather than guess.

One more asymmetry to memorise: SELECT aliases are invisible to WHERE and GROUP BY, but MySQL and PostgreSQL both accept them in HAVING and ORDER BY. So ORDER BY total DESC works while WHERE total > 100 fails with "unknown column".

2. WHERE vs HAVING: Row Filters and Group Filters

The distinction is one sentence: WHERE filters rows, HAVING filters groups. WHERE picks which records enter the aggregation; HAVING picks which aggregated results come out — "customers who spent more than 200" is a group filter:

SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE SUM(amount) > 200       -- ERROR 1111: Invalid use of group function
GROUP BY customer_id;

The correct version moves the aggregate check into HAVING — and note that both filters can appear in the same query, doing different jobs:

SELECT customer_id,
       SUM(amount) AS total,
       COUNT(*)    AS order_count
FROM orders
WHERE status = 'shipped'      -- row filter: cancelled and pending rows never get grouped
GROUP BY customer_id
HAVING SUM(amount) > 200      -- group filter: keep only groups whose total clears 200
ORDER BY total DESC;

The WHERE clause reduces the working set early, which is exactly why it is also the faster place for any condition that does not involve an aggregate. A predicate in HAVING is evaluated after every group has been built and aggregated, so it cannot prune rows before the work happens. Rewriting HAVING status = 'shipped' as WHERE status = 'shipped' is a pure win whenever it is legal.

HAVING does not require GROUP BY. On its own it treats the entire result as a single group, which is a compact way to write a conditional total:

SELECT SUM(amount) AS revenue
FROM orders
WHERE status = 'shipped'
HAVING SUM(amount) > 1000;   -- returns one row, or no rows at all

3. Grouping by Multiple Columns

Listing several columns in GROUP BY produces one output row per existing combination of their values — the same way a composite key works. Each level added refines the grain of the report:

SELECT region,
       status,
       COUNT(*)              AS orders,
       SUM(amount)           AS revenue,
       ROUND(AVG(amount), 2) AS avg_order
FROM orders
GROUP BY region, status
ORDER BY region, status;

Run against our eight-order sample, the result is:

Only five rows come back: (West, cancelled) never occurs in the data, so no row exists for it. GROUP BY is not a pivot table — it reports combinations that are present, not empty cells. To include zero counts, build the full key list in a CTE or calendar table and LEFT JOIN the aggregate to it.

Both MySQL and PostgreSQL 9.5+ support GROUP BY region, status WITH ROLLUP for subtotal rows, which appends NULL in the rolled-up column and saves a second query for grand totals.

4. COUNT(*), COUNT(col) and COUNT(DISTINCT col)

The five standard aggregates are COUNT, SUM, AVG, MIN and MAX. The one that causes silent wrong answers is COUNT, because three different spellings mean three different things — and the difference is entirely about NULL.

All four of the last group ignore NULL. That is consistent, and it is also where the classic AVG trap hides. Suppose three of the eight orders carry a discount and five have discount = NULL:

SELECT COUNT(*)               AS all_rows,        -- 8
       COUNT(discount)        AS rows_with_disc,  -- 3
       COUNT(DISTINCT region) AS regions,         -- 3
       SUM(amount)            AS revenue,         -- 1320.00
       ROUND(AVG(amount), 2)  AS avg_order,       -- 165.00
       ROUND(AVG(discount), 2) AS avg_discount    -- 11.83  (35.50 / 3, not / 8)
FROM orders;

AVG(discount) divides by 3, the number of non-NULL discounts — not by 8. If your definition of "average discount" is "total discount spread across all orders", you have to say so explicitly:

SELECT ROUND(SUM(discount) / COUNT(*), 2) AS discount_per_order  -- 4.44
FROM orders;

When a group contains only NULL values, SUM, AVG, MIN and MAX all return NULL (not zero), while COUNT returns 0. Wrap them in COALESCE(AVG(discount), 0) if the report expects a number.

5. Aggregating Strings: GROUP_CONCAT and STRING_AGG

Numbers are not the only thing you can aggregate. Collapsing a group's textual values into one delimited string is how you build comma-separated tag lists, status summaries, or audit one-liners per customer without a second query. The function name differs by dialect; the behaviour does not.

-- MySQL
SELECT customer_id,
       GROUP_CONCAT(DISTINCT status ORDER BY status SEPARATOR '|') AS statuses
FROM orders
GROUP BY customer_id;

-- PostgreSQL (also SQL Server 2017+, with STRING_AGG)
SELECT customer_id,
       STRING_AGG(DISTINCT status, '|' ORDER BY status) AS statuses
FROM orders
GROUP BY customer_id;

Customer 102, who has a pending and a shipped order, comes back as pending|shipped. Note the argument order difference: MySQL takes its separator as a keyword argument, PostgreSQL as the second positional argument (Oracle calls it LISTAGG).

One MySQL trap is worth pinning down: GROUP_CONCAT silently truncates at group_concat_max_len, which defaults to 1024 bytes. No warning, no error — just a string that stops in the middle. If the concatenated list can be long, raise it for the session first:

SET SESSION group_concat_max_len = 100000;

Unlike window functions, string aggregation has no per-row equivalent in MySQL — collapsing a group into one string is a genuinely aggregate-only operation.

6. Five Mistakes That Break Grouped Queries

Mistake 1: Selecting a column that is neither grouped nor aggregated

SELECT region, status, SUM(amount) AS revenue
FROM orders
GROUP BY region;   -- ERROR 1055 (42000): 'orders.status' isn't in GROUP BY

Every region has several statuses, so there is no single value to print. MySQL 5.7.5+ enables ONLY_FULL_GROUP_BY by default and rejects it; PostgreSQL has always rejected it with "column must appear in the GROUP BY clause or be used in an aggregate function". Older MySQL builds allowed it and returned an arbitrary row's value — a silent bug worth upgrading away from. The fix is to add the column to GROUP BY or wrap it: GROUP_CONCAT(DISTINCT status), MIN(status), or a GROUP BY region, status.

Mistake 2: An aggregate in the WHERE clause

SELECT region, SUM(amount)
FROM orders
WHERE SUM(amount) > 500    -- ERROR 1111: Invalid use of group function
GROUP BY region;

Move it to HAVING. If the condition must run before grouping for some reason, compute it in a subquery or CTE and filter the outer query instead.

Mistake 3: Assuming COUNT(column) means "rows"

SELECT COUNT(coupon_code) AS orders_with_coupon,   -- 3
       COUNT(*)           AS orders                -- 8
FROM orders;

Both statements look like a row count. Only one is. Reach for COUNT(*) unless you specifically want non-NULL values.

Mistake 4: Using a SELECT alias in WHERE

SELECT region, SUM(amount) AS revenue
FROM orders
WHERE revenue > 500        -- ERROR 1054: Unknown column 'revenue' in 'where clause'
GROUP BY region;

The alias does not exist yet when WHERE runs. Use the full expression, or move the alias reference to HAVING or ORDER BY, where MySQL and PostgreSQL both resolve it.

Mistake 5: Putting a row filter in HAVING

-- Works, but aggregates everything before discarding cancelled rows
SELECT region, SUM(amount) AS revenue
FROM orders
GROUP BY region
HAVING region != 'West' AND MAX(status) != 'cancelled';

-- Same result, less work: prune the rows first
SELECT region, SUM(amount) AS revenue
FROM orders
WHERE region != 'West' AND status != 'cancelled'
GROUP BY region;

HAVING conditions that do not involve an aggregate are almost always meant to be WHERE conditions. There is one exception: HAVING is the only clause where a SELECT alias from an aggregate is legal, so HAVING revenue > 500 is a legitimate shortcut.

7. Full Worked Example: A Region Revenue Report

Here is the whole toolkit in one query. It counts shipped orders per region, counts distinct buyers, totals revenue, averages order size, and keeps only regions that cleared 200 in shipped revenue:

SELECT region,
       COUNT(*)                    AS orders,
       COUNT(DISTINCT customer_id) AS buyers,
       SUM(amount)                 AS revenue,
       ROUND(AVG(amount), 2)       AS avg_order,
       MIN(amount)                 AS smallest_order,
       MAX(amount)                 AS largest_order
FROM orders
WHERE status = 'shipped'          -- only completed sales count
GROUP BY region
HAVING SUM(amount) >= 200         -- drop low-volume regions
ORDER BY revenue DESC;

The sample data ships five orders; West totals only 140 and is filtered out by HAVING:

Two things in that output are worth pausing on. North shows orders = 2 but buyers = 1: customer 101 placed both, so COUNT(DISTINCT customer_id) collapses them while COUNT(*) does not — a one-line way to spot repeat buyers. And West's two shipped orders still exist in the table; they are simply missing from the report, because HAVING ran after grouping and removed the group.

Now move the revenue threshold into WHERE and watch it break. WHERE SUM(amount) >= 200 GROUP BY region is invalid for the same reason as Mistake 2, and the workaround people reach for — filtering on individual amount rows — answers a different question entirely. HAVING SUM(...) compares the group total; WHERE amount >= 200 compares single orders and would keep just one row from North. That distinction is the entire reason the clause exists.

When the query grows to five or six of these, the clause order is the first thing a reviewer checks — run it through the online SQL formatter to make the FROM → WHERE → GROUP BY → HAVING → ORDER BY flow visible, and check the plan with the EXPLAIN viewer if a grouped query over a large table starts to drag.

FAQ — SQL GROUP BY and HAVING