HAVING

HAVING is a filter clause that operates on groups after GROUP BY has formed them and after aggregate functions have been computed. WHERE is a filter clause that operates on individual rows before GROUP BY processes them. Both filter data, but they work at different stages of query execution and on different things.

The distinction follows directly from SQL's execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE runs at step 2, before groups exist. HAVING runs at step 4, after groups and their aggregates are fully computed. This means WHERE can only reference raw column values — the aggregate functions have not run yet. HAVING can reference aggregate results — the groups exist and their metrics are known.

Concrete example: to find stores with more than 5 delivered orders, you need WHERE order_status = 'Delivered' to filter individual orders (a row-level condition), GROUP BY store_id to group them by store, and HAVING COUNT(*) > 5 to keep only groups with more than 5 rows. Using WHERE COUNT(*) > 5 is a syntax error — WHERE cannot reference aggregates. Using HAVING order_status = 'Delivered' instead of WHERE works technically but is inefficient — it groups all orders including cancelled ones, then discards the non-Delivered groups after the grouping work is done. Always put row-level conditions in WHERE and aggregate conditions in HAVING.

Yes — HAVING without GROUP BY is syntactically valid in SQL. When HAVING appears without GROUP BY, the entire table (or the rows surviving the WHERE filter) is treated as a single group. HAVING then decides whether that single group appears in the result. If the HAVING condition is TRUE for the aggregate over all rows, the query returns one summary row. If FALSE, the query returns zero rows.

Example: SELECT COUNT(*), SUM(total_amount) FROM orders HAVING COUNT(*) > 100 returns the count and sum if there are more than 100 orders, and returns nothing if there are 100 or fewer. This is technically correct SQL.

In practice, HAVING without GROUP BY is rarely used or recommended. The same result can almost always be expressed more clearly with a subquery or WHERE clause. SELECT COUNT(*), SUM(total_amount) FROM orders WHERE (SELECT COUNT(*) FROM orders) > 100 or putting the condition in application code after checking the count. HAVING without GROUP BY exists in the SQL standard and databases support it, but you will rarely see it in production code and it can confuse readers who expect HAVING to accompany GROUP BY.

This requires two aggregate conditions on the same group — COUNT of orders and AVG of order values — so both belong in HAVING. The WHERE clause filters to only delivered orders (a row-level condition that should happen before grouping for efficiency).

The query: SELECT c.customer_id, c.first_name || ' ' || c.last_name AS customer, COUNT(o.order_id) AS order_count, ROUND(AVG(o.total_amount), 2) AS avg_order_value FROM customers AS c JOIN orders AS o ON c.customer_id = o.customer_id WHERE o.order_status = 'Delivered' GROUP BY c.customer_id, c.first_name, c.last_name HAVING COUNT(o.order_id) > 2 AND AVG(o.total_amount) > 500 ORDER BY avg_order_value DESC.

The reasoning for each clause: JOIN brings orders alongside customer data. WHERE filters to delivered orders before grouping — this is a row-level condition. GROUP BY customer_id (plus name columns per the non-aggregate rule) creates one group per customer. HAVING COUNT > 2 filters to customers with more than two orders. HAVING AVG > 500 filters to customers whose average order exceeds ₹500. Both HAVING conditions must be satisfied simultaneously (AND). ORDER BY sorts by average order value descending — highest-value customers first. This query pattern — filter by count AND filter by aggregate value — is one of the most common HAVING patterns in customer analytics.

Using HAVING for a condition that could be expressed in WHERE is a performance mistake that causes unnecessary work. HAVING filters groups after GROUP BY has already processed all rows. If a non-aggregate condition belongs in WHERE but is mistakenly placed in HAVING, the database groups all rows — including those that would be excluded by the condition — before discarding them at the HAVING stage. All the grouping computation for those excluded rows is wasted.

Concrete example with scale: a table with 10 million orders, 2 million of which are 'Delivered'. HAVING order_status = 'Delivered' after GROUP BY processes all 10 million rows and forms groups for all statuses, then discards non-Delivered groups. WHERE order_status = 'Delivered' filters first — GROUP BY processes only 2 million rows. The WHERE version does 80% less grouping work on this data distribution.

The rule: move any condition expressible on individual row values from HAVING to WHERE. Only conditions requiring aggregate function results belong in HAVING. A quick test: if the condition does not contain COUNT, SUM, AVG, MIN, MAX, or any aggregate function, it should be in WHERE. If it does contain an aggregate, it must be in HAVING. Following this rule consistently keeps GROUP BY queries at their most efficient regardless of table size.

This requires comparing a per-product aggregate (how many orders contain this product) against an overall aggregate (total number of orders). It is a HAVING filter where the threshold is itself a computed value — not a hardcoded number.

Approach 1 — subquery in HAVING: SELECT product_id, COUNT(DISTINCT order_id) AS order_count FROM order_items GROUP BY product_id HAVING COUNT(DISTINCT order_id) > (SELECT COUNT(*) * 0.5 FROM orders). The subquery (SELECT COUNT(*) * 0.5 FROM orders) computes half the total order count and is evaluated once. HAVING then compares each product's order count against this threshold.

Approach 2 — window function (more elegant): SELECT product_id, COUNT(DISTINCT order_id) AS order_count, COUNT(DISTINCT order_id) * 1.0 / COUNT(DISTINCT order_id) OVER () AS fraction_of_orders FROM order_items GROUP BY product_id HAVING COUNT(DISTINCT order_id) * 1.0 / (SELECT COUNT(*) FROM orders) > 0.5. The subquery approach is simpler and more universally supported. In practice, "appears in more than X% of all orders" is called market basket penetration rate — a key metric in retail analytics used to identify anchor products that drive traffic. Products with very high penetration (>50%) are typically staples like milk, bread, or cooking oil that customers purchase on almost every visit.

Cause: You tried to use COUNT, SUM, AVG, MIN, or MAX inside a WHERE clause. WHERE runs before GROUP BY in the execution order — aggregate values do not exist yet at the WHERE stage. The database cannot evaluate an aggregate condition on individual rows because aggregates are computed across groups of rows, not single rows.

Fix: Move the aggregate condition to HAVING. HAVING runs after GROUP BY when groups and their aggregates are fully computed. WHERE COUNT(*) > 5 becomes HAVING COUNT(*) > 5. If there is no GROUP BY in your query, add one: GROUP BY the relevant column, then add HAVING. If you genuinely want to filter based on a whole-table aggregate (not per-group), use a subquery in WHERE: WHERE column > (SELECT AVG(column) FROM table).

Cause: The HAVING condition is too restrictive — no group satisfies it. This happens when the threshold in HAVING is higher than any group's aggregate value, when the WHERE clause already filtered too aggressively leaving insufficient rows in any group, or when the GROUP BY columns create too many small groups (each with a low count) and the HAVING COUNT threshold is never reached.

Fix: Debug by temporarily removing the HAVING clause and examining what the aggregates actually are: SELECT store_id, COUNT(*) AS cnt FROM orders GROUP BY store_id ORDER BY cnt DESC. See the maximum COUNT value across all groups. If the maximum is 3 and your HAVING COUNT(*) > 10 was expecting results, the data does not have groups that large. Adjust the HAVING threshold to match realistic data distributions, or check whether the WHERE clause is too restrictive.

Cause: A non-aggregate condition is in HAVING instead of WHERE — forcing the database to group all rows before filtering. For example, HAVING order_status = 'Delivered' instead of WHERE order_status = 'Delivered'. The database must group all rows (including Cancelled, Returned, Processing) into their groups, compute aggregates for all groups, and only then discard the non-Delivered groups. All the grouping work for non-Delivered rows is wasted.

Fix: Move any condition on non-aggregate columns from HAVING to WHERE. Scan the HAVING clause: if any condition does not reference an aggregate function (COUNT, SUM, AVG, MIN, MAX), move it to WHERE. This pre-filters rows before GROUP BY, dramatically reducing the number of rows that need to be grouped. Use EXPLAIN ANALYZE to compare execution plans before and after — the filter-before-group plan will show significantly lower row counts at the GROUP BY node.

Cause: You tried to use a SELECT alias in HAVING: SELECT COUNT(*) AS order_count ... HAVING order_count > 5. HAVING runs before SELECT in the logical execution order — the alias 'order_count' does not exist yet when HAVING evaluates. This is the same reason SELECT aliases cannot be used in WHERE.

Fix: Repeat the aggregate expression in HAVING: HAVING COUNT(*) > 5. Do not reference the alias. In DuckDB and MySQL, using SELECT aliases in HAVING sometimes works due to implementation-specific extensions, but relying on this is not portable to PostgreSQL or standard SQL. For complex expressions repeated in both SELECT and HAVING, use a CTE: WITH agg AS (SELECT store_id, COUNT(*) AS cnt FROM orders GROUP BY store_id) SELECT store_id, cnt FROM agg WHERE cnt > 5 — the outer query can reference the alias because it is now a proper column in the CTE result.

Cause: A condition that should be in WHERE is absent, causing rows that should be excluded to still participate in grouping and be counted by the HAVING aggregate. For example, counting all order statuses when only 'Delivered' orders should count — HAVING COUNT(*) > 3 counts every order, not just delivered ones, so a store with 2 delivered and 2 cancelled orders passes the threshold incorrectly.

Fix: Add the appropriate WHERE condition: WHERE order_status = 'Delivered'. This excludes non-Delivered rows before grouping, so COUNT(*) in HAVING only counts the rows you care about. Alternatively, use conditional counting in HAVING: HAVING SUM(CASE WHEN order_status = 'Delivered' THEN 1 ELSE 0 END) > 3 — this counts only Delivered rows within each group without filtering other rows from the group. Use conditional counting when you need both the filtered count and other metrics (like total orders including all statuses) in the same query.

The FreshCart loyalty team needs to identify customers for a targeted re-engagement campaign. Write a query that finds customers who meet ALL of these criteria: placed at least 2 delivered orders, have an average delivered order value between ₹300 and ₹800 (not too cheap, not already high-value), and their most recent delivered order was before '2024-03-01' (lapsed customers). Show customer_id, full name, city, loyalty_tier, order_count, avg_order_value (rounded to 2 decimal places), and last_order_date. Sort by last_order_date ascending (longest lapsed first).