Excel to SQL · Lesson 7 of 12

Filter a pivot table with HAVING

In Excel, you'd reach for

fxPivot table > Value Filters > Greater Than

Sometimes you only want the buckets whose total passes a test: countries with more than 30 customers, say. You can't use WHERE for that, because WHERE runs on single rows before anything is totalled.

HAVING is WHERE for totals. It comes after GROUP BY.

SQL order of execution: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT, with the Excel equivalent of each step — The order SQL runs a query in, with the Excel step each one is like. Download · Save to Pinterest

Try itEdit the query, then Run

country	customers
United States	130
India	78
United Kingdom	53
Nigeria	31

4 rows

Rule of thumb: filtering raw rows (a date range, one region) is WHERE. Filtering on a SUM or COUNT is HAVING. You can use both in one query.

Your turn

Which customers have placed 3 or more orders? Show customer_id and the count as orders.

This page on one card

Filter a pivot table with HAVING: the Excel way (Pivot table > Value Filters > Greater Than) and the SQL query, side by side — Filter a pivot table with HAVING, on one card: the Excel way and the SQL. Download · Save to Pinterest

The practice data (7 tables)

customers: one row per customer
subscriptions: one row per paid plan
events: one row per thing someone did
orders: one row per order
products: a lookup table
campaigns: one row per channel per month
tickets: one row per support ticket

See every column and sample rows