HAVING usage and examples

Overview

HAVING filters groups after GROUP BY and aggregation, so it can test values such as SUM or COUNT directly. Put source-row predicates in WHERE whenever possible to reduce the data being grouped. Do not use HAVING as a general replacement for WHERE when no aggregate condition is involved.

Basic syntax

SELECT department, SUM(amount) FROM orders GROUP BY department HAVING SUM(amount) > 1000;

Examples

HAVING example

Aggregate amount by department, then keep only departments whose SUM(amount) is greater than 1000.

Sample table

Table：orders

orders
department	amount
Sales	1200
IT	900
Sales	1500
HR	700

Run result

result
department	SUM(amount)
Sales	2700

Common issues

Can HAVING be used without GROUP BY?

Some databases allow it, but in practice HAVING is usually used with GROUP BY and aggregate functions.

Can HAVING replace WHERE?

It should not. Put row-level filters in WHERE when possible.

Can HAVING use aggregate functions?

Yes. This is a common use case, such as HAVING COUNT(*) > 1.

Keep reading

GROUP BY COUNT SUM WHERE AVG MAX MIN COUNT DISTINCT

HAVING