HAVING

HAVING filters groups after aggregation; anything that can be decided per row belongs in WHERE, where an index can help.

SyntaxSELECT ... FROM ... [WHERE ...] GROUP BY ... HAVING aggregate_or_group_condition

HAVING filters the rows produced by GROUP BY — one decision per group, after aggregation. WHERE filters the input rows, before any grouping happens. That ordering is the whole lesson: HAVING can refer to aggregate functions, and WHERE cannot.

SELECT user, MAX(salary) FROM users
  GROUP BY user HAVING MAX(salary) > 10;

Why the same predicate belongs in WHERE when it can

The manual is explicit that HAVING is applied nearly last, just before rows are sent to the client, with no optimization. A condition on a plain column put in HAVING therefore reads and groups every row first and throws the work away at the end; the same condition in WHERE can use an index and cut the rows before grouping. Do not write HAVING status = 'paid'; write WHERE status = 'paid'. A query often needs both: WHERE to choose which rows count, HAVING to choose which groups survive.

Extensions and the alias trap

Standard SQL restricts HAVING to grouped columns and aggregates. MySQL extends it to reference select-list columns and columns from outer subqueries too, so HAVING c = 1 works when the select list says COUNT(name) AS c. If a name is both an alias and a real column in GROUP BY, MySQL warns about the ambiguity and gives preference to the GROUP BY column, not your alias.

Without GROUP BY, all rows form one group, so HAVING then filters a single-row result.

Example

Example · sql
-- Customers worth chasing: paid orders only (WHERE),
-- and only those with repeat business worth over 500 (HAVING).
SELECT
    c.id,
    c.email,
    COUNT(*)              AS paid_orders,
    SUM(o.amount_cents)   AS lifetime_cents
FROM customers AS c
JOIN orders    AS o ON o.customer_id = c.id
WHERE o.status   = 'paid'                 -- per-row: indexable, cuts rows before grouping
  AND o.placed_at >= '2025-01-01'
GROUP BY c.id, c.email
HAVING COUNT(*) >= 2                      -- per-group: cannot be known before aggregation
   AND SUM(o.amount_cents) > 50000
ORDER BY lifetime_cents DESC
LIMIT 100;                                -- LIMIT is applied after HAVING

When to use it

  • List customers with more than one paid order and a lifetime value above a threshold.
  • Find duplicate keys before adding a UNIQUE constraint.
  • Show only days on which error counts exceeded an alert threshold.
  • Return a single summary row only when a total crosses a limit.

More examples

The same filter in the wrong place and the right place

Both return identical rows, which is exactly why the slow one survives code review. Compare them with EXPLAIN: the WHERE version shows a ref or range access on the index, the HAVING version a full scan. Any predicate that mentions no aggregate should move up to WHERE.

Example · sql
-- SLOW: groups every order in the table, then discards most groups.
SELECT customer_id, SUM(amount_cents) AS total
FROM orders
GROUP BY customer_id
HAVING customer_id IN (17, 42, 99);

-- FAST: the predicate is per-row, so it belongs in WHERE and can use the
-- primary/secondary index on customer_id before any grouping happens.
SELECT customer_id, SUM(amount_cents) AS total
FROM orders
WHERE customer_id IN (17, 42, 99)
GROUP BY customer_id;

Finding duplicates — the canonical HAVING query

COUNT(*) > 1 cannot be evaluated per row, so this is a genuine HAVING. It is the query you run before adding a UNIQUE index, since the index creation will fail on exactly these groups.

Example · sql
-- Which emails appear more than once, and which rows are involved?
SELECT
    LOWER(email)                    AS email_key,
    COUNT(*)                        AS copies,
    MIN(id)                         AS keep_id,          -- oldest row
    GROUP_CONCAT(id ORDER BY id)    AS all_ids
FROM customers
WHERE deleted_at IS NULL            -- ignore soft-deleted rows entirely
GROUP BY email_key
HAVING COUNT(*) > 1
ORDER BY copies DESC;

HAVING with no GROUP BY, and the alias ambiguity

The first form is useful for threshold alerts: no GROUP BY means one implicit group, so the query returns nothing unless the condition holds. The last pair is the trap — never give an aggregate alias the same name as a real column you are grouping by.

Example · sql
-- All rows are one group, so this returns either one row or none.
SELECT SUM(amount_cents) AS total
FROM orders
WHERE placed_at >= CURRENT_DATE
HAVING SUM(amount_cents) > 100000;    -- "alert me only if today beat 1000 units"

-- MySQL extension: an alias in HAVING works...
SELECT name, COUNT(name) AS c FROM orders GROUP BY name HAVING c = 1;

-- ...but when the name is BOTH an alias and a GROUP BY column, MySQL warns
-- and resolves to the GROUP BY column, not your alias.
SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

Related

sql group bysql wheresql countsql sum avgsql filter clause

Discussion

  • Be the first to comment on this lesson.