Lesson 4 of 14 · 7 min · Summarising data

GROUP BY and HAVING: summarising data

Covers: PostgreSQL 18 syntax; standard SQL that also runs on MySQL 8. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

What you will learn

  • The aggregate functions COUNT, SUM, AVG, MIN and MAX
  • Grouping rows with GROUP BY
  • WHERE versus HAVING
  • 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 BY column.
  • 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

  • WHERE filters rows before grouping.
  • HAVING filters 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(*) > 1 is an error; use HAVING).
  • Filtering with HAVING what belongs in WHERE, 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

  1. Find the total and average order amount per customer.
  2. List customers whose total paid amount is above 1,000.
  3. Count orders per month and per status in one query.
  4. Show the percentage of orders that are paid (hint: COUNT(*) FILTER (...) or a CASE, divided by COUNT(*), 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?
  1. 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 BY makes one summary per group.
  • Every selected column must be grouped or aggregated.
  • WHERE filters rows before grouping; HAVING filters groups after.
  • CASE inside an aggregate gives conditional totals; group dates by month to see trends.

Going deeper

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.