SQL GROUP BY and HAVING explained for beginners, with real output

Your manager asks, "How much did each city spend?" Not one total for the whole company, but one number per city. That is what GROUP BY is for. It splits a table into groups and runs an aggregate inside each group. An aggregate is a function such as SUM or COUNT that turns many rows into one number.

This guide builds up from one total to one total per group. The queries ran in DuckDB, and each output is exactly what it printed. If SUM or COUNT are new, read SQL for beginners first.

The table

10 orders from four cities. Two details are on purpose: one order has no city (the customer did not give one), and one order is refunded.

CREATE TABLE orders (
  order_id INTEGER,
  customer VARCHAR,
  city     VARCHAR,
  amount   INTEGER,
  status   VARCHAR
);

INSERT INTO orders VALUES
  (1,  'Asha',  'Pune',      1200, 'paid'),
  (2,  'Ravi',  'Mumbai',     800, 'paid'),
  (3,  'Meena', 'Chennai',    450, 'refunded'),
  (4,  'Kiran', 'Pune',      2500, 'paid'),
  (5,  'Asha',  'Pune',       300, 'paid'),
  (6,  'Divya', NULL,        1500, 'pending'),
  (7,  'Ravi',  'Mumbai',     950, 'paid'),
  (8,  'Meena', 'Chennai',   2200, 'paid'),
  (9,  'Imran', 'Mumbai',     600, 'paid'),
  (10, 'Sana',  'Chennai',    700, 'paid');

1One answer for the whole table

Without GROUP BY, an aggregate folds the whole table into a single row:

SELECT COUNT(*) AS orders, SUM(amount) AS total
FROM orders;

orders | total
-------+------
10     | 11200
(1 row)

10 orders, a total of 11200. Useful, but it cannot say which city brought in what.

2GROUP BY: one answer per group

Add GROUP BY city. The database sorts the rows into one pile per city and runs COUNT and SUM on each pile.

SELECT city, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
GROUP BY city
ORDER BY total DESC;

city    | orders | total
--------+--------+------
Pune    | 3      | 4000
Chennai | 3      | 3350
Mumbai  | 3      | 2350
NULL    | 1      | 1500
(4 rows)

1 row per city. The row with an empty city is the order where the city is NULL. GROUP BY puts all NULLs into one group of their own, which is why you see a row for them rather than losing them.

3The rule that causes most errors

Every column in SELECT must be either in the GROUP BY or inside an aggregate function. If it is neither, the database cannot decide which value to show. Look at the error when you break the rule:

SELECT city, customer, SUM(amount) AS total
FROM orders
GROUP BY city;

Binder Error: column "customer" must appear in the GROUP BY clause or must be part of an aggregate function.

customer is not in the GROUP BY and is not aggregated, so there are several customers in the Pune pile and the database has no way to pick one. Either add customer to the GROUP BY (which makes smaller groups) or remove it. Some databases such as MySQL used to hide this error and return a random customer, which is worse.

4Group by more than 1 column

List more than 1 column to get one group per combination:

SELECT city, status, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
GROUP BY city, status
ORDER BY city, status;

city    | status   | orders | total
--------+----------+--------+------
Chennai | paid     | 2      | 2900
Chennai | refunded | 1      | 450
Mumbai  | paid     | 3      | 2350
Pune    | paid     | 3      | 4000
NULL    | pending  | 1      | 1500
(5 rows)

Now Chennai is split in two, because it has one refunded order and two paid ones. The more columns you group by, the smaller the groups get.

5WHERE and HAVING: filtering before and after

WHERE removes rows before they are grouped. Use it to leave out refunded orders from the totals:

SELECT city, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY city
ORDER BY city;

city    | orders | total
--------+--------+------
Chennai | 2      | 2900
Mumbai  | 3      | 2350
Pune    | 3      | 4000
(3 rows)

The refunded Chennai order is gone, so Chennai now has 2 orders. What if you want to keep only the cities whose total is above 2500? The total does not exist until the groups are made, so WHERE cannot see it. Use HAVING, which filters groups after they are formed:

SELECT city, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY city
HAVING SUM(amount) > 2500
ORDER BY total DESC;

city    | total
--------+------
Pune    | 4000
Chennai | 2900
(2 rows)

Pune (4000) and Chennai (2900) pass, and Mumbai (2350) does not. The order the database works in is: FROM, then WHERE (filter rows), then GROUP BY (make groups), then HAVING (filter groups), then SELECT, then ORDER BY. A simple rule: a condition on a single row goes in WHERE, and a condition on an aggregate goes in HAVING.

SELECT city, SUM(amount) AS total
FROM orders
WHERE SUM(amount) > 2500
GROUP BY city;

Binder Error: WHERE clause cannot contain aggregates!

That is the error you get when you put an aggregate in WHERE.

6Three kinds of COUNT

COUNT has three forms, and beginners mix them up. They differ when the data has NULLs or repeats:

SELECT COUNT(*)               AS all_rows,
       COUNT(city)            AS rows_with_a_city,
       COUNT(DISTINCT city)   AS different_cities,
       COUNT(DISTINCT customer) AS different_customers
FROM orders;

all_rows | rows_with_a_city | different_cities | different_customers
---------+------------------+------------------+--------------------
10       | 9                | 3                | 7
(1 row)

COUNT(*) counts every row, 10. COUNT(city) skips the row where the city is NULL, so 9. COUNT(DISTINCT city) counts each city once, so 3 (Pune, Mumbai and Chennai). The last one counts 7 different customers, because Asha, Ravi and Meena each placed 2 orders.

7Count something only when a condition is true

A handy trick is to add up a condition. Here, how many orders per city were paid, and how many were not:

SELECT city,
       SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
       SUM(CASE WHEN status <> 'paid' THEN 1 ELSE 0 END) AS other_orders
FROM orders
WHERE city IS NOT NULL
GROUP BY city
ORDER BY city;

city    | paid_orders | other_orders
--------+-------------+-------------
Chennai | 2           | 1
Mumbai  | 3           | 0
Pune    | 3           | 0
(3 rows)

CASE WHEN is SQL's "if". For each row it returns 1 when the condition is true and 0 when it is not, and SUM adds the ones up. You get 2 columns of counts from one pass over the table.

Quick checklist

1. 1Which column do I want one answer per? That goes in GROUP BY.
2. 2Is every other SELECT column inside an aggregate?
3. 3Do I need to drop rows first? That is WHERE.
4. 4Do I need to drop groups afterwards? That is HAVING.
5. 5Could the grouping column contain NULL, and do I want that group?

Practise with Average salary by department and Departments with more than three people, both free. After that, joins let you group across 2 tables.