HAVING Clause

Introduction

HAVING filters groups, not rows. It runs after GROUP BY. Use WHERE to filter rows before grouping; HAVING to filter the resulting groups.

Understanding the topic

Core concepts to understand:

• Runs after GROUP BY.
• Can reference aggregates: HAVING sum(amount) > 1000.
• Cannot reference column aliases in standard SQL (Postgres allows it).
• Combine with WHERE to filter both rows and groups.
• Equivalent to a wrapper subquery on the grouped result.

Syntax reference

Visual workflow / architecture:

WHERE  status = 'paid'           -- row filter
GROUP  BY user_id
HAVING sum(amount) > 1000        -- group filter
ORDER  BY sum(amount) DESC;

SELECT <group_col>, <agg_func>(<col>) AS <alias>
FROM   <table>
GROUP  BY <group_col>
HAVING <agg_predicate>;

SELECT plan, COUNT(*) AS users
FROM   users
GROUP  BY plan
HAVING COUNT(*) >= 3;

Real-world use

Show top-spending customers, products with > 100 sales, regions with > 5 errors — classic HAVING queries.

Best practices

• Push as much as possible into WHERE for performance.
• Use HAVING only for aggregate filters.
• Index columns used in WHERE/GROUP BY.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Difference between HAVING and WHERE.
• Q2. Order of execution: WHERE/GROUP BY/HAVING.
• Q3. Can HAVING use aggregates?
• Q4. Can WHERE use aggregates?
• Q5. Example using both.