Skip to content
Back to DatabasesAll lessons

Databases

HAVING (filter groups, after GROUP BY)

O(n) to build the groups — the same scan as GROUP BY — plus O(k) to test k groups against the HAVING condition.

The idea, in plain English

WHERE filters rows before they are bundled up, like sorting mail before you tie it into bundles. HAVING filters the bundles themselves, after grouping. This is like weighing each finished bundle and keeping only the heavy ones. Use it for 'customers whose total spend is over $50', because that total does not exist until after GROUP BY has run.

How it works

  1. 1First, GROUP BY as usual. Bucket rows by some column, such as customer, and compute aggregates like COUNT(*) and SUM(amount) for each bucket.
  2. 2Then apply the HAVING condition to each bucket's aggregate result, not to the original rows. For example: 'keep this bucket only if its total is over 50'.
  3. 3Drop any bucket that fails the condition. What is left is your final result: one row per surviving group.

When you'd use it

Use HAVING any time your filter depends on an aggregate, not a raw column. For example: 'customers who ordered more than 3 times', or 'days with total sales over $1000'. A plain WHERE cannot do this, because SUM() and COUNT() do not exist yet when WHERE runs.

Common beginner mistakes

  • Do not write WHERE SUM(amount) > 50. Real SQL rejects this, because WHERE runs before aggregation happens. HAVING is the clause built for filtering on aggregates.
  • HAVING filters whole groups, not individual rows. You can still use WHERE first to filter raw rows, then GROUP BY, then HAVING to filter the resulting groups.

Try it — edit and run

Click the code to edit · press ⌘/Ctrl+↵ to run

Editable code. Tab and Shift+Tab indent. Press Escape, then Tab, to move focus out of the editor.

Expected output — hit Run to try it
All groups (before HAVING):
Ana orders=2 total=70
Bilal orders=2 total=40
Cara orders=1 total=70
SQL: SELECT customer, COUNT(*) AS orders, SUM(amount) AS total FROM orders GROUP BY customer HAVING SUM(amount) > 50
Groups after HAVING SUM(amount) > 50:
Ana orders=2 total=70
Cara orders=1 total=70

Not sure this is the right topic? See the learning paths → or where this leads →