Aggregation Updated November 2026

HAVING

HAVING filters groups after GROUP BY, using aggregates that WHERE cannot see: SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id HAVING COUNT(*) > 1 returns customers with more than one order. WHERE filters rows before grouping; HAVING filters the groups afterwards.

Syntax

SELECT group_column, AGG(column) FROM table GROUP BY group_column HAVING AGG(column) condition;

Examples

SELECT customer_id, COUNT(*) AS orders FROM orders GROUP BY customer_id HAVING COUNT(*) >= 2;

Result: Repeat buyers and their order counts

SELECT customer_id, SUM(amount) AS spent
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) > 1000
ORDER BY spent DESC;

Result: Customers who paid more than 1000 in total, biggest first

WHERE removes unpaid orders first, then HAVING keeps the big spenders.

SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1;

Result: Duplicate email addresses

The standard way to find duplicates.

Common mistakes

Using HAVING without GROUP BY to filter rows; it works (the whole table is one group) but WHERE is what you want for row conditions.
Putting a row condition like HAVING status = 'paid' makes PostgreSQL complain that status must appear in GROUP BY; move row conditions to WHERE.
Writing HAVING before GROUP BY is a syntax error; HAVING follows GROUP BY.

Practice HAVING

These drills check your answer on live data, free, no account needed.

Repeat buyersReturn the customer_id of every customer who placed more than two orders, whatever the status.
Departments above averageReturn the dept_id and average salary of every department whose average salary is above the average salary of all employees. Ignore employees without a department when grouping.

Questions

When do I use HAVING instead of WHERE?

Use WHERE for conditions on individual rows and HAVING for conditions on aggregates (COUNT, SUM, AVG, MIN, MAX) computed per group. A query can have both.

Can HAVING use a column that is not in SELECT?

Yes, HAVING can use any aggregate, even one not shown: SELECT dept FROM employees GROUP BY dept HAVING COUNT(*) > 10.

GROUP BYGROUP BY collapses rows that share a value into one row per group so you can count, sum or average them. One or several columns, the must-appear rule, HAVING.
COUNTCOUNT(*) counts rows, COUNT(column) counts non-NULL values, COUNT(DISTINCT column) counts unique values. Examples with WHERE, GROUP BY and conditional counts.
WHEREUse WHERE to keep only the rows that match a condition: equals, greater than, text matches, NULL checks, and combinations with AND, OR and NOT.
SUM, AVG, MIN, MAXThe core aggregates: SUM adds values, AVG averages them, MIN and MAX find the smallest and largest. NULL handling, integer division in AVG, use with GROUP BY.