Lesson 4 of 14 · 7 min · Summarising data
GROUP BY and HAVING: summarising data
What you will learn
- The aggregate functions
COUNT,SUM,AVG,MINandMAX - Grouping rows with
GROUP BY WHEREversusHAVING- Grouping by month and conditional counts with
CASE - The rule for columns in a grouped
SELECT
In simple words
Imagine a pile of till receipts. Adding up the whole pile gives one total. Sorting them into piles by day and adding each pile gives a total per day. GROUP BY is that sorting into piles, and the aggregate functions are the adding up. HAVING then lets you keep only the piles that meet a rule, for example "only days with more than five receipts".
Words to know
- Aggregate function: a function that turns many rows into one value, such as
SUM. - Group: the set of rows that share the same value in the
GROUP BYcolumn. - HAVING: a filter applied to groups after they are formed.
- Alias: the name you give a result column with
AS.
Aggregate functions
SELECT COUNT(*) AS orders, SUM(amount) AS total, AVG(amount) AS average,
MIN(amount) AS smallest, MAX(amount) AS largest
FROM orders;
orders | total | average | smallest | largest
--------+---------+-----------------------+----------+---------
6 | 5500.00 | 916.6666666666666667 | 500.00 | 1500.00
With no GROUP BY, the whole table is one group and you get one row. Round for display with ROUND(AVG(amount), 2).
GROUP BY
Add GROUP BY to get one row per group:
SELECT status, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
GROUP BY status;
status | orders | total
----------+--------+---------
paid | 4 | 3100.00
refunded | 1 | 1500.00
pending | 1 | 900.00
(Row order is not guaranteed; add ORDER BY total DESC for a stable order.) You can group by several columns, which makes one group per combination: GROUP BY customer_id, status.
The rule: every column in SELECT must either be in GROUP BY or be inside an aggregate function. SELECT customer_id, amount ... GROUP BY customer_id is an error in PostgreSQL, because a customer has several amounts and the database cannot pick one. (MySQL may allow it depending on ONLY_FULL_GROUP_BY, returning an arbitrary value, which is a bug waiting to happen.)
WHERE versus HAVING
WHEREfilters rows before grouping.HAVINGfilters groups after aggregating, so it can use aggregate functions.
-- customers with at least two orders
SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2;
returns customer 1 (three orders) and customer 3 (two). To total only paid orders and then keep customers whose paid total is over 700:
SELECT customer_id, SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) > 700;
Customer 1 has paid orders 1200 + 800 + 500 = 2500, so it is kept; customer 3 has one paid order of 600, so it is not. Put a condition in WHERE whenever it does not need an aggregate: it is cheaper because rows are removed before the grouping work.
Grouping by month
Analysts group by time constantly. In PostgreSQL:
SELECT DATE_TRUNC('month', order_date) AS month, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;
month | orders | total
---------------------+--------+---------
2025-09-01 00:00:00 | 3 | 3500.00
2025-10-01 00:00:00 | 3 | 2000.00
In MySQL the equivalent is DATE_FORMAT(order_date, '%Y-%m'). Use EXTRACT(YEAR FROM order_date) for the year or month number.
Conditional aggregation
CASE inside an aggregate makes several measures in one pass, a pivot-style summary:
SELECT customer_id,
SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid,
SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS pending,
COUNT(*) FILTER (WHERE status = 'refunded') AS refunds
FROM orders
GROUP BY customer_id;
FILTER (WHERE ...) is the PostgreSQL shortcut; SUM(CASE ...) works everywhere. Customer 3 shows paid 600 and pending 900.
COUNT(DISTINCT)
COUNT(DISTINCT customer_id) counts unique customers who ordered (3). This is how you avoid counting the same person once per order.
Common mistakes
- Selecting a column that is neither grouped nor aggregated.
- Using an aggregate in
WHERE(WHERE COUNT(*) > 1is an error; useHAVING). - Filtering with
HAVINGwhat belongs inWHERE, which is slower and harder to read. - Grouping by a text alias that differs from the real column and getting unexpected groups.
- Counting rows when you meant to count distinct entities.
Try it yourself
- Find the total and average order amount per customer.
- List customers whose total paid amount is above 1,000.
- Count orders per month and per status in one query.
- Show the percentage of orders that are paid (hint:
COUNT(*) FILTER (...)or aCASE, divided byCOUNT(*), cast to decimal).
Check yourself
What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping. HAVING filters groups after aggregation, so it can use aggregates such as COUNT(*).
Why is SELECT customer_id, amount FROM orders GROUP BY customer_id an error?
amount is neither grouped nor aggregated, so there is no single value to show per customer. Aggregate it or add it to GROUP BY.
What does COUNT(*) return for a group of 4 rows, 1 of which has a NULL amount?
- It counts rows;
COUNT(amount)would return 3.
How do you count unique customers rather than orders?
COUNT(DISTINCT customer_id).
Interview questions
Write a query for the top 3 customers by total spend.
SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id ORDER BY total DESC LIMIT 3;
Find customers with more than one order.
SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) > 1;
How do you get month-wise sales?
Group by the month part of the date: DATE_TRUNC('month', order_date) in PostgreSQL or DATE_FORMAT(order_date, '%Y-%m') in MySQL, then SUM(amount), and ORDER BY the month.
Summary
- Aggregates (
COUNT,SUM,AVG,MIN,MAX) summarise rows;GROUP BYmakes one summary per group. - Every selected column must be grouped or aggregated.
WHEREfilters rows before grouping;HAVINGfilters groups after.CASEinside an aggregate gives conditional totals; group dates by month to see trends.
Going deeper
- Aggregate functions: the full list.
- GROUP BY and HAVING: the PostgreSQL manual.
- MySQL GROUP BY handling:
ONLY_FULL_GROUP_BYexplained. - Date/time functions:
DATE_TRUNCandEXTRACT.
See how much of this you can explain
A free mock interview asks you new questions on SQL for analysts and developers for Data Analyst and scores every answer, with what to fix.