GROUP BY collapses many rows into one summary row per group, and HAVING then throws away the groups you don't want. You write GROUP BY first and HAVING right after it, and HAVING can test the totals that GROUP BY has just calculated. That is the whole idea. The rest of this guide shows how to apply it without the errors that trip up most people the first week.
We'll use one small orders table throughout, show how HAVING differs from WHERE, cover grouping by several columns, and end with a debugging routine you can reuse on any database.
Suppose you have this orders table:
| order_id | customer_id | region | status | amount |
| 1 | 101 | West | paid | 250.00 |
| 2 | 101 | West | paid | 1,300.00 |
| 3 | 102 | East | refunded | 90.00 |
| 4 | 103 | East | paid | 4,800.00 |
| 5 | 103 | East | paid | 700.00 |
| 6 | 103 | West | paid | 120.00 |
| 7 | 104 | West | pending | 60.00 |
Without GROUP BY, an aggregate like COUNT(*) summarizes the whole table into a single number. With it, you get one summary row per group. PostgreSQL's documentation describes it as combining rows that share the same value, or the same combination of values, into one group row. Common aggregates are COUNT, SUM, AVG, MAX and MIN.
SELECT customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;
Result on our data:
| customer_id | order_count | total_spent |
| 101 | 2 | 1550.00 |
| 102 | 1 | 90.00 |
| 103 | 3 | 5620.00 |
| 104 | 1 | 60.00 |
The general pattern is:
SELECT grouping_column, aggregate_function(measure)
FROM table_name
[WHERE row_condition]
GROUP BY grouping_column
[HAVING aggregate_condition]
[ORDER BY ...];
If you come from spreadsheets, this is exactly what a PivotTable does when you drop a field in Rows and another in Values. Our PivotTable walkthrough shows that same summary in thirty seconds, which is a good mental bridge if you know Excel better than SQL.
GROUP BY alone returns every group. Often you only want some of them: customers with more than one order, regions with over $5,000 in sales, products that sold fewer than ten units. That is HAVING's job.
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;
This returns customers 101 and 103. Customers 102 and 104 each have one order, so their groups are removed entirely. HAVING doesn't leave an empty placeholder row; the group simply disappears.
HAVING works well for exception reports. Microsoft's Transact-SQL documentation for HAVING uses this pattern to list sales orders whose subtotal exceeds $100,000.00, turning a broad summary into a short list of orders worth a look. Our version:
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000
ORDER BY total_spent DESC;
That returns customer 103 (5,620.00) and customer 101 (1,550.00).
Note that we repeated SUM(amount) in HAVING instead of writing total_spent. That's deliberate, and it matters; see the alias section below.
This is the most common confusion, and it's worth getting exactly right.
-- Error: aggregate not allowed in WHERE
SELECT customer_id, SUM(amount)
FROM orders
WHERE SUM(amount) > 1000
GROUP BY customer_id;
Move the aggregate condition to HAVING and it works. Conversely, a condition about a single row, such as status = 'paid', belongs in WHERE.
The two clauses are not rivals. They do different jobs in the same query:
SELECT customer_id, SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) > 1000;
WHERE first removes the refunded and pending rows, so only paid orders feed the SUM. HAVING then keeps customers whose paid total exceeds 1,000. The output is customers 101 (1,550.00) and 103 (5,620.00).
PostgreSQL's mental model is the key: WHERE controls which rows enter an aggregate, HAVING decides which finished groups survive. So moving a condition between them can change the totals, not just the speed.
Compare two queries on customer 103:
-- A: filter rows first
SELECT customer_id, SUM(amount) FROM orders
WHERE region = 'East'
GROUP BY customer_id;
-- B: group first, filter groups
SELECT customer_id, SUM(amount) FROM orders
GROUP BY customer_id
HAVING MIN(region) = 'East';
Query A sums only customer 103's East orders (5,500.00). Query B, if it kept the group, would sum all of their orders including the West one (5,620.00). Same customer, different number, because the filter ran at a different stage.
HAVING can legally test a plain grouped column. Databricks documents examples like HAVING city = 'Fremont' next to an aggregate test. But if a condition doesn't depend on aggregation, put it in WHERE. PostgreSQL explains that WHERE reduces the rows entering the grouping step, so using HAVING for a row-level test can force extra grouping work. It's also easier to read.
| Question | Use |
| Only paid orders? | WHERE |
| Only orders after January 1? | WHERE |
| Customers with at least 3 orders? | HAVING |
| Regions with total sales above 5,000? | HAVING |
| Average order value above 500? | HAVING |
In the written query, GROUP BY comes before HAVING. In logical terms, WHERE filters the input rows, GROUP BY forms groups and computes aggregates, HAVING filters the groups, and ORDER BY sorts what's left. SQL isn't evaluated simply top to bottom, which is why you can't use a SELECT alias in WHERE in most engines: the SELECT list is processed after the filtering.
A handy order to remember:
List more than one column and you get one group per unique combination of values.
SELECT region, status,
COUNT(*) AS orders,
SUM(amount) AS total
FROM orders
GROUP BY region, status;
On our data:
| region | status | orders | total |
| West | paid | 3 | 1670.00 |
| West | pending | 1 | 60.00 |
| East | refunded | 1 | 90.00 |
| East | paid | 2 | 5500.00 |
SELECT region, status, SUM(amount) AS total
FROM orders
GROUP BY region, status
HAVING SUM(amount) > 1000;
This returns West/paid and East/paid. The more columns you add, the finer the grain and the smaller each group, so thresholds that made sense for one column may filter out everything when you add a second.
This is the classic:
SELECT customer_id, order_date, COUNT(*)
FROM orders
GROUP BY customer_id;
PostgreSQL explains the problem: a customer group has many order dates, and no single one is defined for the group row. You have three honest options: add order_date to GROUP BY (changing the grain), wrap it in an aggregate such as MAX(order_date), or remove it.
The strictest cross-database habit is to select only grouping columns and aggregate expressions. Databricks raises a MISSING_AGGREGATION error for ungrouped, nonaggregated references. Some databases, SQLite among them, are more permissive and will return an arbitrary value instead of an error. Don't rely on that if your SQL has to run elsewhere.
Dialects differ. Some engines let you refer to an aggregate alias such as total_spent in HAVING, while others require the full expression or an outer query. Databricks' documentation shows that dialect rules matter, so test in your target database rather than assuming one engine's behavior is universal. Repeating the aggregate expression, as we did above, works everywhere.
Sometimes you want the metric in the output and then need to filter or reuse it. A derived table or CTE is a readable alternative:
WITH customer_totals AS (
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
)
SELECT *
FROM customer_totals
WHERE total_spent > 1000;
This is logically equivalent to HAVING, and many people find it easier to extend, because the alias is an ordinary column in the outer query.
PostgreSQL notes that grouping without aggregate expressions effectively produces distinct values. That's fine when you need a list of unique categories, but when you're building metrics, an explicit COUNT or SUM states your intent better.
When a grouped query returns something surprising, don't stare at it. Follow this sequence:
Group-and-filter logic is not unique to SQL. In Excel, SUMIFS and PivotTables do the same job: summarize by category, then keep only what matters. Our Day 3 lesson on SUMIFS walks through summarizing by a condition, which maps neatly to WHERE plus GROUP BY. If you want sample tables to experiment with, our guide to free Excel sample data has datasets by domain, and our page on SQL practice exercises with solutions collects problems to try.
We build LogicExcel around one belief: you don't learn aggregation by watching someone else do it. You learn it by typing the formula, seeing the result change, and being told exactly why a wrong answer was wrong. Our exercises run in an Excel-like environment with instant feedback, with no account and no installation, so the loop between attempt and correction takes seconds.
GROUP BY and HAVING are a perfect example of why that matters. The syntax fits on a sticky note. What takes practice is the judgment: is this condition about a row or a group? Did my filter run before or after the total? Those instincts come from making the mistake once on a small table and seeing the numbers move. Analysts and managers who build their own reports tend to need exactly this fluency, whether the tool is a SQL editor or a spreadsheet.
Our advice: start with a tiny dataset where you can add up every group in your head, write the query, predict the output before you run it, then check. Repeat with WHERE moved to HAVING and see what changes. That ten-minute habit beats an hour of reading.
> — Thomas Houza
If your day-to-day tool is Excel, or you're moving toward data work from a spreadsheet background, try our free Excel practice exercises. Every exercise gives instant feedback and explains each mistake, and you can start without creating an account. When you're ready to go deeper, the Excel courses take you from basics to advanced summarizing and reporting.
GROUP BY combines rows that share the same value, or combination of values, into one group and returns one summary row per group. HAVING then removes the groups whose aggregate or grouped condition is false. For example, GROUP BY customer_id HAVING COUNT(*) > 1 returns only customers with more than one order.
Yes, in some databases. SQL Server and Databricks both treat a query with HAVING but no GROUP BY as a single group covering the whole result. It's uncommon, and it's usually clearer to use a GROUP BY or a plain WHERE, depending on what you mean.
It depends on the database. Some engines accept an aggregate alias in HAVING, and others require you to repeat the aggregate expression or filter in an outer query. Repeating the expression, for example HAVING SUM(amount) > 1000, is the portable choice.
If the condition applies to individual rows, use WHERE. It reduces the rows entering the grouping step, which is cleaner and avoids unnecessary aggregation work. Save HAVING for conditions that need a group result, such as a total or a count.
Not for which rows land in which group. Groups are defined by the combination of all listed columns, so GROUP BY region, status and GROUP BY status, region form identical groups. Only the display order may differ, so add ORDER BY when you need a specific one.
Guides people read next.
Master SQL quickly with structured practice exercises with solutions. Improve your skills from beginner to advanced, step by step.
9 minLearn how to use the COUNT function in Excel with real example formulas and practice exercises.
6 minThe IF function in Excel returns one value if a condition is true, and another if the condition is false. Learn about this formula and practice some exercises.
7 min