SQL GROUP BY and HAVING: Build Useful Summary Reports

A grand total is useful, but it rarely answers every question. You may need a total for each customer, each city or each month. SQL GROUP BY builds those smaller summaries, and HAVING lets you keep only the groups that meet a rule.

Practice setup: These lessons use SQLite and the fictional shop database from the QueryFun SQL practice lab. Open that lab, run its setup once in a fresh practice database, then return here. Do not run the INSERT statements again for each lesson. You can use SQLite Fiddle without installing a database server.

The customers are Amina (1, Nairobi), Brian (2, Mombasa) and Chao (3, Nairobi). Orders are 101 (customer 1, amount 1200), 102 (customer 2, amount 800), 103 (customer 1, amount 600) and 104 (customer 3, amount 1500). Amounts are fictional currency units. Each order belongs to one customer. A customer can have several orders.

Decide what one output row means

Before writing the query, complete this sentence: “Each row in my result represents one …” For this example, the missing words are customer with at least one order. That tells us to group orders by customer_id.

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Customer 1 has 2 orders totalling 1800. Customer 2 has 1 order totalling 800. Customer 3 has 1 order totalling 1500. GROUP BY collects input rows with the same customer_id, then calculates each aggregate inside that group.

Make the selected columns match the groups

The customer_id is meaningful because it is the grouping key. order_id would not be a useful single value for customer 1's group, which contains orders 101 and 103. Either aggregate a column or include it in the intended grouping.

Adding order_id to GROUP BY changes the question. Instead of one row per customer, you would get one row per customer-and-order combination. Do not add columns merely to silence an error. First decide what the report should represent.

Filter groups with HAVING

Suppose you only want customers whose combined order amount is at least 1500:

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) >= 1500
ORDER BY total_amount DESC, customer_id;

Customers 1 and 3 remain. Customer 1 qualifies because 1200 plus 600 equals 1800, even though neither individual order reaches 1500. HAVING checks the completed group, so it can use an aggregate such as SUM(amount).

See why WHERE answers a different question

Compare the previous query with this one:

SELECT customer_id, SUM(amount) AS total_amount
FROM orders
WHERE amount >= 1500
GROUP BY customer_id
ORDER BY customer_id;

Only customer 3 appears, with 1500. WHERE removes smaller orders before the grouping happens. Both of customer 1's orders disappear, so there is no 1800 total to test later. WHERE means “which source rows count?” HAVING means “which completed groups should appear?”

You can use both when the business question requires both rules. For example, filter source rows to a particular period with WHERE, group them by customer, and then use HAVING to find customers above a spending threshold during that period.

Reconcile the summary with the detail

Add the group totals: 1800 + 800 + 1500 = 4100. Add the group order counts: 2 + 1 + 1 = 4. They match the grand totals from the original four orders. This is a useful habit whenever you build a new grouped report.

A grouped report from orders cannot show a customer who has no order rows. That is not a zero group; it is an absent group. A later LEFT JOIN lesson shows how to keep those customers explicitly.

Try it yourself

Find customers with at least two orders. Return customer_id, order_count and average_amount. Sort by customer_id. Then explain why WHERE COUNT(*) >= 2 is not the correct place for the count condition.

SELECT customer_id,
       COUNT(*) AS order_count,
       AVG(amount) AS average_amount
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_id;

Keep learning: Return to the SQL learning path to choose your next lesson.

Further reading: SQLite SELECT documentation.