GROUP BY forms summary groups, WHERE filters input rows before grouping, and HAVING filters the resulting groups by a group-level condition.
Customers with more than one sale is not an ordinary row filter: one sale row does not know the customer’s total count. Grouping calculates that count, and HAVING decides which customer groups meet the threshold.
This reference adds detail to the main course. You can read it independently: the small database below supplies every table and row used in the examples. Start with the question each query answers, then compare the returned values with the data rather than memorising a line of syntax.
Set up the practice database
Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.
Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(40) NOT NULL,
city VARCHAR(40),
manager_id INTEGER
);
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount DECIMAL(10,2),
status VARCHAR(20) NOT NULL,
created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
(1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
(3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
(101,1,10,'paid','2026-01-01'),
(102,1,30,'pending','2026-01-02'),
(103,2,20,'paid','2026-01-02'),
(104,3,NULL,'pending','2026-02-01'),
(105,3,20,'paid','2026-02-02'),
(106,NULL,5,'paid','2026-02-03');An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.
The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.
How GROUP BY and HAVING works
Define the summary grain first. GROUP BY customer_id means one result per customer link. GROUP BY customer_id,status means one per customer-and-status combination, creating more groups. Adding a date can turn a lifetime summary into a daily summary. Every grouping expression changes the question, so include only the dimensions actually required by the report. Grouping on a name alone can merge different people who happen to share that name.
WHERE runs conceptually before grouping and determines which source rows participate. HAVING runs on groups and can test aggregates such as COUNT() >= 2 or SUM(amount) > 30. A paid-sale WHERE followed by HAVING COUNT() >= 2 identifies customers with two paid sales, not customers with two sales overall. Moving the status filter after grouping is not equivalent when it changes which rows contribute to the count.
Which columns can be selected
Select grouped columns and aggregate expressions that describe the chosen grain. Some engines recognise functional dependencies, for example that a primary key determines the other columns of a table. The safest portable beginner query groups explicitly by all selected nonaggregate expressions. Do not rely on permissive selection of an arbitrary value from the group. MySQL’s ONLY_FULL_GROUP_BY mode and SQLite’s permissive bare-column behaviour can make the same-looking query accepted in one environment and rejected in another.
NULL values form a group when the grouping expression is NULL. That means the anonymous customer link gets a group unless you filter it out or start from identified customers. Aggregate NULL handling still applies inside each group. HAVING SUM(amount) > 10 excludes a group whose sum is unknown, because the comparison is unknown. COUNT(*) and COUNT(amount) can distinguish its row population from its known amounts. Grouping does not sort the output; add ORDER BY separately.
MySQL, PostgreSQL, SQL Server and SQLite
GROUP BY and HAVING are supported throughout these systems. Select-list aliases in HAVING are an extension in some engines rather than a portable assumption; repeat the aggregate or introduce an outer query. ROLLUP, CUBE and GROUPING SETS support varies and should be checked separately from ordinary grouping.
Check the engine-specific syntax and version requirements in PostgreSQL grouping and HAVING.
Practice task and worked solution
Find statuses with more than two recorded sales, and show their record counts.
Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.
SELECT status,COUNT(*) AS sale_count FROM sales
GROUP BY status HAVING COUNT(*)>2 ORDER BY status;status | sale_count
paid | 4The paid group contains four rows and the pending group contains two. HAVING tests the completed group counts, retaining only paid. No row-level status filter is necessary because the task compares all status groups against the same threshold.
Move the paid-status rule into WHERE and rerun the count-at-least-two customer summary: no identified customer currently has two paid sales. Compare that empty result with the all-status summary containing Ana and Cara. Next group by both customer_id and status to see how an extra dimension splits the previous customer groups into smaller units.
SQL GROUP BY and HAVING: Advanced Grouping Examples Syntax
GROUP BY and HAVING
SELECT customer_id,COUNT(*) AS sale_count,SUM(amount) AS total
FROM sales WHERE customer_id IS NOT NULL
GROUP BY customer_id HAVING COUNT(*)>=2
ORDER BY customer_id;SQL GROUP BY and HAVING: Advanced Grouping Examples Summary
GROUP BYdefines the grain of the result: one row per distinct combination of its expressions.WHEREfilters rows before grouping;HAVINGfilters groups using aggregate results such asCOUNT(*).- Select only grouped expressions and aggregates, so every output column has one clear value per group.
- Adding an extra column to
GROUP BYsplits groups, such as one customer into one row per amount. GROUP BYdoesn't sort; addORDER BYfor the order you want.
SQL GROUP BY and HAVING: Advanced Grouping Examples Examples
Each example shows the code, the output it produces and what each part does.
Customers with at least two sales
Only identified customer sales enter the grouping. Ana and Cara each have two records, so HAVING keeps their groups. Cara’s missing amount still counts as a sale, but SUM ignores it and reports the known total twenty.
SELECT customer_id,COUNT(*) AS sale_count,SUM(amount) AS total
FROM sales WHERE customer_id IS NOT NULL
GROUP BY customer_id HAVING COUNT(*)>=2
ORDER BY customer_id;customer_id | sale_count | total
1 | 2 | 40
3 | 2 | 20Common SQL GROUP BY and HAVING: Advanced Grouping Examples Mistakes
Filtering an aggregate in WHERE
WHERE COUNT(*) >= 2 attempts to filter before the group count exists and fails. HAVING belongs to the grouped query. Another mistake is grouping by customer_id and amount when the goal is one row per customer; the extra amount dimension fragments the customer into several groups.
SQL GROUP BY and HAVING: Advanced Grouping Examples in Real Projects
Repeat customers in a loyalty report
A loyalty report can identify customers with multiple recorded transactions while showing both the transaction count and the known spending total. If qualifying purchases must be paid, put that rule in WHERE and update the report label. Keep missing-amount information visible when spending determines eligibility.
Write the grouping grain in the report specification, then check every selected nonaggregate field against it. Eligibility conditions applied before grouping change the observations used by every aggregate. A transaction-count threshold should explicitly say whether it counts all statuses or only paid sales, because the two reports can select different customers.
SQL GROUP BY and HAVING: Advanced Grouping Examples FAQ
Can WHERE and HAVING appear together?
Yes. WHERE restricts the input rows, for example to paid sales, and HAVING restricts the resulting summaries, for example to customers with at least two of them. Put every condition that doesn't need an aggregate in WHERE, which also lets the database discard rows early.
Does GROUP BY sort rows?
No. Any apparent order is implementation behaviour that can change with indexes, data size or database version, so request presentation order explicitly with ORDER BY.
Why did one customer become several output rows?
Inspect every grouping expression; each distinct combination creates a separate group. Grouping by customer_id, amount gives one row per customer and amount. Keep only the columns that define the grain you want in GROUP BY, and aggregate the rest.
What are ROLLUP and GROUPING SETS?
They add subtotal and grand-total rows in one query. GROUP BY ROLLUP (customer_id, status) returns totals per customer and status, per customer, and overall. PostgreSQL, SQL Server and Oracle support ROLLUP, CUBE and GROUPING SETS; MySQL supports WITH ROLLUP; SQLite doesn't support them.
Can I group by an expression such as the month?
Yes. Group by the same expression you select, such as strftime('%Y-%m', created_on) in SQLite or DATE_TRUNC('month', created_on) in PostgreSQL. Include the year so the same month in different years stays separate.
SQL GROUP BY and HAVING: Advanced Grouping Examples Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Which customers pass HAVING COUNT(*)>=2 in the identified-sales summary?
SQL GROUP BY and HAVING: Advanced Grouping Examples Quiz
2 questions to check what you learned.
Where should a COUNT(*) threshold be tested?
- In HAVING after grouping
- In WHERE before the groups exist
- In ORDER BY, which filters low-count groups
How many recorded sales belong to Cara?
- One, because a missing amount is not a sale
- Two, including the sale with an unknown amount
- Zero, because any NULL amount invalidates the whole customer group