SQL HAVING Clause

HAVING with COUNT

The most common HAVING pattern in production and interviews. You group rows, count them, then keep only groups that meet a threshold. This is how you find active users, high-volume products, or departments with staffing problems.

-- Find customers who placed more than 10 orders
SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(total) AS lifetime_value
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 10
ORDER BY order_count DESC;

HAVING with SUM and AVG

Filtering on aggregated totals or averages. This pattern drives business questions like: which regions generate enough revenue to justify a local office? Which product categories have an average price above a certain tier?

-- Regions with total revenue above $100K
-- and average order value above $50
SELECT
  region,
  SUM(amount) AS total_revenue,
  ROUND(AVG(amount), 2) AS avg_order_value,
  COUNT(*) AS order_count
FROM orders
GROUP BY region
HAVING SUM(amount) > 100000
   AND AVG(amount) > 50;

HAVING with WHERE (Combined Filtering)

WHERE filters rows before grouping. HAVING filters groups after. Using both in the same query is the standard pattern for two-stage filtering. WHERE reduces the dataset first, which makes GROUP BY faster because it processes fewer rows.

-- Among completed orders from 2024,
-- find users who spent more than $1000
SELECT
  user_id,
  COUNT(*) AS completed_orders,
  SUM(amount) AS total_spent
FROM orders
WHERE status = 'completed'
  AND order_date >= '2024-01-01'
GROUP BY user_id
HAVING SUM(amount) > 1000
ORDER BY total_spent DESC;

HAVING with Expressions

HAVING is not limited to simple comparisons. You can use arithmetic, CASE expressions, and even subqueries inside it. This pattern handles questions like: find groups where the spread between max and min exceeds a threshold, or groups where the top value is more than 3x the average.

-- Find product categories where the price spread
-- (max minus min) exceeds $500
SELECT
  category,
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive,
  MAX(price) - MIN(price) AS price_spread,
  COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING MAX(price) - MIN(price) > 500
ORDER BY price_spread DESC;

Using HAVING for Row-Level Filters

Putting a non-aggregate condition in HAVING when it belongs in WHERE. The query still runs (HAVING can filter on non-aggregate columns in most engines), but it forces the engine to group all rows first. On a table with millions of rows across 50 regions, filtering one region in HAVING means grouping all 50, then throwing away 49.

-- BAD: groups all regions, then discards non-US
SELECT region, SUM(amount)
FROM orders
GROUP BY region
HAVING region = 'US';

-- GOOD: filters to US rows first, then groups
SELECT region, SUM(amount)
FROM orders
WHERE region = 'US'
GROUP BY region;

This mistake signals to the interviewer that you do not understand SQL execution order. It is one of the fastest ways to get a “no hire” signal on a SQL round.

Referencing SELECT Aliases in HAVING

In standard SQL, HAVING evaluates before SELECT in the logical plan. Aliases defined in SELECT do not exist when HAVING runs. MySQL allows this as a non-standard extension, which creates bad habits. PostgreSQL, SQL Server, BigQuery, Snowflake, and Trino all reject it.

-- FAILS in PostgreSQL, BigQuery, Snowflake
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING emp_count > 10;

-- WORKS everywhere
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

Repeat the full aggregate expression in HAVING. It looks redundant, but it is correct SQL. The optimizer is smart enough not to compute it twice.

Forgetting HAVING When Filtering Aggregates

Attempting to put an aggregate in WHERE. This is a syntax error in every SQL engine. WHERE cannot reference aggregate functions because it runs before GROUP BY. The fix is always to move the condition to HAVING.

-- ERROR: aggregate functions not allowed in WHERE
SELECT department, AVG(salary)
FROM employees
GROUP BY department
WHERE AVG(salary) > 80000;

-- FIX: use HAVING for aggregate conditions
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 80000;

This error usually happens under time pressure when candidates write quickly and forget to check clause order. Slow down in interviews. Write the clauses in execution order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.