SQL: JOIN, GROUP BY and aggregates on PostgreSQL 17

In short

JOIN combines rows of two tables by a condition: INNER JOIN keeps only the pairs that found each other, while LEFT JOIN keeps every row of the left table and fills in NULL when there is no match. Aggregate functions (count, sum, avg, min, max) fold many rows into one value and skip NULL, except count(*). GROUP BY computes aggregates separately for each group; WHERE filters rows before grouping, and HAVING filters the finished groups after it.

TutorialLevel: BeginnerSQLPractice: ~45–90 min

After this you will be able to

Join two or more tables with INNER JOIN and LEFT JOIN and find rows without a match, such as clients with no orders
Compute totals with count, sum, avg, min, max and explain how they treat NULL
Group rows with GROUP BY, filter groups with HAVING and explain how HAVING differs from WHERE
Split a complex report into steps with WITH and spot duplicate rows after a JOIN

Know beforehand

SQL from scratch: SELECT, WHERE, ORDER BY

Verified with

PostgreSQL 17 (17.11, образ postgres:17-alpine)psql 17Docker Engine 29

Checked against docs: October 11, 2026

Contents

01Why JOIN: the data lives in different tables
02INNER JOIN and LEFT JOIN, side by side
03Aggregates: COUNT, SUM, AVG, MIN, MAX and NULL
04GROUP BY and HAVING
05Subqueries and CTEs (WITH)
06Common mistakes: duplicates after JOIN and COUNT(*) vs COUNT(column)
07Exercises: revenue by product and clients without orders

Why JOIN: the data lives in different tables

In "SQL from scratch" we worked with one table at a time. But in the training store database an order keeps only the client number client_id, while the client's name lives in clients. That is by design: client data is written once, and orders reference it. To see an order together with the name, the tables are combined with a JOIN query.

If the training database container has already been stopped, start it again with the same shop.sql script:

bash

# shop.sql is the script from the "SQL from scratch" lesson
docker run -d --rm --name shop-db -e POSTGRES_PASSWORD=test postgres:17-alpine
docker exec -i shop-db psql -U postgres < shop.sql
docker exec -it shop-db psql -U postgres -P null=NULL

SQL

SELECT o.id, o.ordered_on, o.status, c.name
FROM orders AS o
JOIN clients AS c ON c.id = o.client_id
ORDER BY o.id;

id	ordered_on	status	name
1	2026-09-02	shipped	Aigerim Seitova
2	2026-09-05	shipped	Daniyar Akhmetov
3	2026-09-14	paid	Aigerim Seitova
4	2026-09-20	cancelled	Madina Omarova
5	2026-09-28	shipped	Timur Kim

ordered_on: 2026-09-02
status: shipped
name: Aigerim Seitova

ordered_on: 2026-09-05
status: shipped
name: Daniyar Akhmetov

ordered_on: 2026-09-14
status: paid
name: Aigerim Seitova

ordered_on: 2026-09-20
status: cancelled
name: Madina Omarova

ordered_on: 2026-09-28
status: shipped
name: Timur Kim

Real psql output. Shortened: first 5 of 10 rows.

How to read it: for each row of orders the database looks for rows of clients for which the condition after ON is true, and glues them into one result row. AS o and AS c are short table aliases. The PostgreSQL documentation considers it good style to qualify every column in a join query: if both tables end up with a column of the same name, such as id or name, an unqualified query breaks.

A query can contain several joins. An order line references both an order and a product, so a receipt needs all four tables:

SQL

SELECT o.id AS order_id, c.name AS client, p.name AS product,
       oi.quantity, oi.price, oi.quantity * oi.price AS line_total
FROM orders AS o
JOIN clients AS c      ON c.id = o.client_id
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p     ON p.id = oi.product_id
WHERE o.id IN (1, 5)
ORDER BY o.id, p.name;

order_id	client	product	quantity	price	line_total
1	Aigerim Seitova	Ceramic Mug 350 ml	2	2500.00	5000.00
1	Aigerim Seitova	Espresso Blend 1 kg	1	12900.00	12900.00
5	Timur Kim	Ceramic Mug 350 ml	1	2500.00	2500.00
5	Timur Kim	Ethiopia Yirgacheffe 250 g	1	5600.00	5600.00
5	Timur Kim	Masala Chai 200 g	2	3800.00	7600.00

client: Aigerim Seitova
product: Ceramic Mug 350 ml
quantity: 2
price: 2500.00
line_total: 5000.00

client: Aigerim Seitova
product: Espresso Blend 1 kg
quantity: 1
price: 12900.00
line_total: 12900.00

client: Timur Kim
product: Ceramic Mug 350 ml
quantity: 1
price: 2500.00
line_total: 2500.00

client: Timur Kim
product: Ethiopia Yirgacheffe 250 g
quantity: 1
price: 5600.00
line_total: 5600.00

client: Timur Kim
product: Masala Chai 200 g
quantity: 2
price: 3800.00
line_total: 7600.00

Lines of orders 1 and 5 with product names and line totals.

INNER JOIN and LEFT JOIN, side by side

A bare JOIN means INNER JOIN. Only pairs that found each other make it into the result. Clients with no orders at all silently disappear from such a query:

SQL

SELECT c.id, c.name, o.id AS order_id, o.status
FROM clients AS c
JOIN orders AS o ON o.client_id = c.id
ORDER BY c.id, o.id;

INNER JOIN: 10 rows; clients 7 and 8 are missing because they have no orders.

SQL

SELECT c.id, c.name, o.id AS order_id, o.status
FROM clients AS c
LEFT JOIN orders AS o ON o.client_id = c.id
ORDER BY c.id, o.id;

id	name	order_id	status
1	Aigerim Seitova	1	shipped
1	Aigerim Seitova	3	paid
1	Aigerim Seitova	9	paid
2	Daniyar Akhmetov	2	shipped
2	Daniyar Akhmetov	10	new
3	Madina Omarova	4	cancelled
3	Madina Omarova	7	shipped
4	Timur Kim	5	shipped
5	Aliya Bekova	6	paid
6	Ruslan Ivanov	8	new
7	Zarina Kaliyeva	NULL	NULL
8	Sergey Pak	NULL	NULL

name: Aigerim Seitova
order_id: 1
status: shipped

name: Aigerim Seitova
order_id: 3
status: paid

name: Aigerim Seitova
order_id: 9
status: paid

name: Daniyar Akhmetov
order_id: 2
status: shipped

name: Daniyar Akhmetov
order_id: 10
status: new

name: Madina Omarova
order_id: 4
status: cancelled

name: Madina Omarova
order_id: 7
status: shipped

name: Timur Kim
order_id: 5
status: shipped

name: Aliya Bekova
order_id: 6
status: paid

name: Ruslan Ivanov
order_id: 8
status: new

name: Zarina Kaliyeva
order_id: NULL
status: NULL

name: Sergey Pak
order_id: NULL
status: NULL

LEFT JOIN: 12 rows. The last two are clients without orders; the order columns are NULL.

INNER JOIN

Only rows that found a match by the ON condition
Rows without a match in either table are dropped
Report: "orders with client names"

LEFT JOIN

Every row of the left table at least once
With no match, the right table's columns are NULL
Report: "all clients and their orders, if any"; finding rows without a match

The documentation describes LEFT JOIN exactly this way: an inner join is performed first, then for each left-table row without a match a row with NULL in the right-table columns is added. Hence the classic technique for finding rows without a match: keep only the rows where the right table's key is NULL.

SQL

SELECT c.id, c.name, c.created_on
FROM clients AS c
LEFT JOIN orders AS o ON o.client_id = c.id
WHERE o.id IS NULL
ORDER BY c.id;

id	name	created_on
7	Zarina Kaliyeva	2026-09-10
8	Sergey Pak	2026-09-30

name: Zarina Kaliyeva
created_on: 2026-09-10

name: Sergey Pak
created_on: 2026-09-30

Clients without a single order.

SQL

SELECT p.id, p.name
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL;

id	name
9	Gift Box

name: Gift Box

A product nobody has ordered.

There are also RIGHT JOIN (like LEFT, but keeps the right table) and FULL JOIN (keeps rows of both). In practice RIGHT JOIN is usually rewritten as LEFT JOIN with the tables swapped — the query is easier to read.

SQL

-- Intent: every client plus their paid orders
SELECT c.name, o.id AS order_id, o.status
FROM clients AS c
LEFT JOIN orders AS o ON o.client_id = c.id
WHERE o.status = 'paid'
ORDER BY c.id, o.id;

name	order_id	status
Aigerim Seitova	3	paid
Aigerim Seitova	9	paid
Aliya Bekova	6	paid

Aigerim Seitova

order_id: 3
status: paid

Aigerim Seitova

order_id: 9
status: paid

Aliya Bekova

order_id: 6
status: paid

Wrong: 3 rows left; clients without paid orders are gone.

SQL

SELECT c.name, o.id AS order_id, o.status
FROM clients AS c
LEFT JOIN orders AS o ON o.client_id = c.id AND o.status = 'paid'
ORDER BY c.id, o.id;

name	order_id	status
Aigerim Seitova	3	paid
Aigerim Seitova	9	paid
Daniyar Akhmetov	NULL	NULL
Madina Omarova	NULL	NULL
Timur Kim	NULL	NULL
Aliya Bekova	6	paid
Ruslan Ivanov	NULL	NULL
Zarina Kaliyeva	NULL	NULL
Sergey Pak	NULL	NULL

Aigerim Seitova

order_id: 3
status: paid

Aigerim Seitova

order_id: 9
status: paid

Daniyar Akhmetov

order_id: NULL
status: NULL

Madina Omarova

order_id: NULL
status: NULL

Timur Kim

order_id: NULL
status: NULL

Aliya Bekova

order_id: 6
status: paid

Ruslan Ivanov

order_id: NULL
status: NULL

Zarina Kaliyeva

order_id: NULL
status: NULL

Sergey Pak

order_id: NULL
status: NULL

Condition in ON: all 8 clients; Aigerim has two paid orders — 9 rows.

Aggregates: COUNT, SUM, AVG, MIN, MAX and NULL

An aggregate function computes a single value from many rows. Without GROUP BY the whole result is one group, and the query returns exactly one row.

SQL

SELECT count(*)        AS products,
       count(weight_g) AS with_weight,
       min(price)      AS cheapest,
       max(price)      AS most_expensive,
       round(avg(price), 2) AS avg_price,
       avg(weight_g)   AS avg_weight
FROM products;

products	with_weight	cheapest	most_expensive	avg_price	avg_weight
9	5	2500.00	18500.00	8366.67	360.0000000000000000

with_weight: 5
cheapest: 2500.00
most_expensive: 18500.00
avg_price: 8366.67
avg_weight: 360.0000000000000000

Real output: 9 products, weight set for 5.

This shows the main rule: aggregates skip NULL. count(*) counts rows, while count(weight_g) counts only rows where the weight is set. avg(weight_g) is the average over the five products with a known weight: (1000 + 250 + 250 + 100 + 200) / 5 = 360, not the sum divided by 9. That is almost always what you want, but keep it in mind when NULL means "zero" rather than "unknown".

Expression	What it counts	Result on orders
`count(*)`	All rows in the group	10
`count(promo_code)`	Rows where the value is not `NULL`	3
`count(DISTINCT promo_code)`	Distinct values, excluding `NULL`	2

count(*)

What it counts: All rows in the group
Result on orders: 10

count(promo_code)

What it counts: Rows where the value is not NULL
Result on orders: 3

count(DISTINCT promo_code)

What it counts: Distinct values, excluding NULL
Result on orders: 2

SQL

SELECT count(*) AS orders, count(promo_code) AS with_promo,
       count(DISTINCT promo_code) AS distinct_promos
FROM orders;

--  orders | with_promo | distinct_promos
-- --------+------------+-----------------
--      10 |          3 |               2
-- (1 row)

Checking the table above on the training database.

The second trap: according to the documentation, all aggregates except count return NULL when there are no rows at all. The sum of an empty set is not zero. Order 10 has no lines yet:

SQL

SELECT sum(quantity * price) AS revenue
FROM order_items
WHERE order_id = 10;
SELECT coalesce(sum(quantity * price), 0) AS revenue
FROM order_items
WHERE order_id = 10;
SELECT count(*) AS lines
FROM order_items
WHERE order_id = 10;

--  revenue
-- ---------
--     NULL
-- (1 row)
--
--  revenue
-- ---------
--        0
-- (1 row)
--
--  lines
-- -------
--      0
-- (1 row)

sum over an empty set is NULL, coalesce turns it into 0, count returns 0.

Another useful tool is FILTER: it limits the input rows of one particular aggregate without touching the others. That way one query computes several metrics:

SQL

SELECT count(*) AS orders,
       count(*) FILTER (WHERE status = 'cancelled') AS cancelled,
       count(*) FILTER (WHERE promo_code IS NOT NULL) AS with_promo
FROM orders;

orders	cancelled	with_promo
10	1	3

cancelled: 1
with_promo: 3

Total, cancelled and promo orders in one pass.

GROUP BY and HAVING

GROUP BY splits rows into groups with equal values and computes aggregates for each group separately. Revenue by category means joining order lines with orders and products, filtering by status and grouping by category. In this lesson revenue counts paid and shipped orders: new ones are not paid yet, cancelled ones did not happen.

SQL

SELECT p.category,
       sum(oi.quantity)            AS units,
       sum(oi.quantity * oi.price) AS revenue
FROM order_items AS oi
JOIN orders AS o   ON o.id = oi.order_id
JOIN products AS p ON p.id = oi.product_id
WHERE o.status IN ('paid', 'shipped')
GROUP BY p.category
ORDER BY revenue DESC;

category	units	revenue
coffee	9	77500.00
equipment	2	27400.00
tea	3	10800.00
tableware	3	7500.00

coffee

units: 9
revenue: 77500.00

equipment

units: 2
revenue: 27400.00

tea

units: 3
revenue: 10800.00

tableware

units: 3
revenue: 7500.00

Revenue by category. There is no gifts row: the Gift Box was never sold.

The grouping rule: SELECT may output only the GROUP BY columns and aggregates. If you output a column with many values inside a group, the database does not know which one to pick and refuses to run the query:

SQL

SELECT p.category, p.name, sum(oi.quantity) AS units
FROM order_items AS oi
JOIN products AS p ON p.id = oi.product_id
GROUP BY p.category;

-- ERROR:  column "p.name" must appear in the GROUP BY clause or be used in an aggregate function
-- LINE 1: SELECT p.category, p.name, sum(oi.quantity) AS units
--                            ^

There is one exception: when you group by the primary key, the other columns of that table may be output — the key determines them unambiguously. So GROUP BY c.id is enough to output c.name. Our examples write GROUP BY c.id, c.name, which is clearer and portable to other DBMSs.

HAVING filters groups that are already built. Let us find clients with two or more orders, not counting cancelled ones:

SQL

SELECT c.name, count(*) AS orders
FROM clients AS c
JOIN orders AS o ON o.client_id = c.id
WHERE o.status <> 'cancelled'
GROUP BY c.id, c.name
HAVING count(*) >= 2
ORDER BY orders DESC, c.name;

name	orders
Aigerim Seitova	3
Daniyar Akhmetov	2

Aigerim Seitova

orders: 3

Daniyar Akhmetov

orders: 2

Madina has two orders, but one is cancelled, so she does not pass the filter.

The difference between WHERE and HAVING follows from the execution order. WHERE decides which rows go into the groups, so no aggregate exists at that point yet:

SQL

SELECT client_id, count(*)
FROM orders
WHERE count(*) >= 2
GROUP BY client_id;

-- ERROR:  aggregate functions are not allowed in WHERE
-- LINE 3: WHERE count(*) >= 2
--               ^

FROM and JOINWHEREGROUP BY and HAVINGSELECTORDER BY and LIMIT

The logical order of SELECT processing per the PostgreSQL documentation: rows are filtered before grouping, groups after it.

The documentation recommends putting everything that does not need an aggregate into WHERE: that way the database does not spend work grouping rows it will drop anyway. HAVING keeps only conditions on aggregates.

When grouping, all NULL values fall into one group. The client without a city forms a separate row:

SQL

SELECT city, count(*) AS clients
FROM clients
GROUP BY city
ORDER BY clients DESC, city;

city	clients
Almaty	3
Astana	2
Karaganda	1
Shymkent	1
NULL	1

Almaty

clients: 3

Astana

clients: 2

Karaganda

clients: 1

Shymkent

clients: 1

NULL

clients: 1

NULL in city is a separate group of one client.

Subqueries and CTEs (WITH)

A subquery is a SELECT inside another query. You cannot write an aggregate in WHERE, but you can compare with a value computed by a separate subquery. The PostgreSQL tutorial shows this technique too:

SQL

SELECT name, price
FROM products
WHERE price > (SELECT avg(price) FROM products)
ORDER BY price DESC;

name	price
Hand Grinder	18500.00
Gift Box	15000.00
Espresso Blend 1 kg	12900.00
French Press 600 ml	8900.00

Hand Grinder

price: 18500.00

Gift Box

price: 15000.00

Espresso Blend 1 kg

price: 12900.00

French Press 600 ml

price: 8900.00

Products priced above the catalogue average (8366.67 KZT).

You can also group by an expression. date_trunc('month', …) truncates a date to the first day of the month, and ::date casts the result to date. Here GROUP BY uses the output column name month — PostgreSQL allows that.

SQL

SELECT date_trunc('month', o.ordered_on)::date AS month,
       count(DISTINCT o.id)                    AS orders,
       sum(oi.quantity * oi.price)             AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.status IN ('paid', 'shipped')
GROUP BY month
ORDER BY month;

month	orders	revenue
2026-09-01	4	55500.00
2026-10-01	3	67700.00

2026-09-01

orders: 4
revenue: 55500.00

2026-10-01

orders: 3
revenue: 67700.00

Revenue by month: September and October 2026.

When a report takes several steps, WITH helps. The documentation describes a CTE (common table expression) as an auxiliary statement that acts like a temporary table for the duration of one query. First we compute each order's total, then revenue and the average order value per client from those totals:

SQL

WITH order_totals AS (
    SELECT o.id, o.client_id, sum(oi.quantity * oi.price) AS total
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.id
    WHERE o.status IN ('paid', 'shipped')
    GROUP BY o.id, o.client_id
)
SELECT c.name,
       count(*)              AS orders,
       sum(t.total)          AS revenue,
       round(avg(t.total))   AS avg_check
FROM order_totals AS t
JOIN clients AS c ON c.id = t.client_id
GROUP BY c.id, c.name
ORDER BY revenue DESC;

name	orders	revenue	avg_check
Aigerim Seitova	3	58200.00	19400
Aliya Bekova	1	25800.00	25800
Timur Kim	1	15700.00	15700
Daniyar Akhmetov	1	13000.00	13000
Madina Omarova	1	10500.00	10500

Aigerim Seitova

orders: 3
revenue: 58200.00
avg_check: 19400

Aliya Bekova

orders: 1
revenue: 25800.00
avg_check: 25800

Timur Kim

orders: 1
revenue: 15700.00
avg_check: 15700

Daniyar Akhmetov

orders: 1
revenue: 13000.00
avg_check: 13000

Madina Omarova

orders: 1
revenue: 10500.00
avg_check: 10500

Step 1: order totals; step 2: per-client results. The average order value is rounded.

SQL

SELECT name FROM products
WHERE id NOT IN (SELECT product_id FROM order_items);
SELECT name FROM products
WHERE id NOT IN (1, 2, 3, 4, 5, 6, 7, 8, NULL);
SELECT p.name FROM products AS p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items AS oi WHERE oi.product_id = p.id
);

--    name
-- ----------
--  Gift Box
-- (1 row)
--
--  name
-- ------
-- (0 rows)
--
--    name
-- ----------
--  Gift Box
-- (1 row)

Three queries in a row; real output in the comments.

Common mistakes: duplicates after JOIN and COUNT(*) vs COUNT(column)

A one-to-many join multiplies rows: each order repeats as many times as it has lines. Count count(*) after that and you get the number of lines, not orders:

SQL

-- Wrong: counts order lines, not orders
SELECT c.name, count(*) AS orders
FROM clients AS c
JOIN orders AS o       ON o.client_id = c.id
JOIN order_items AS oi ON oi.order_id = o.id
GROUP BY c.id, c.name
ORDER BY c.id;

name	orders
Aigerim Seitova	5
Daniyar Akhmetov	2
Madina Omarova	3
Timur Kim	3
Aliya Bekova	1
Ruslan Ivanov	1

Aigerim Seitova

orders: 5

Daniyar Akhmetov

orders: 2

Madina Omarova

orders: 3

Timur Kim

orders: 3

Aliya Bekova

orders: 1

Ruslan Ivanov

orders: 1

Wrong: Aigerim has 3 orders, not 5 — they contain 5 lines.

SQL

SELECT c.name,
       count(DISTINCT o.id)        AS orders,
       sum(oi.quantity * oi.price) AS total
FROM clients AS c
JOIN orders AS o       ON o.client_id = c.id
JOIN order_items AS oi ON oi.order_id = o.id
GROUP BY c.id, c.name
ORDER BY c.id;

name	orders	total
Aigerim Seitova	3	58200.00
Daniyar Akhmetov	1	13000.00
Madina Omarova	2	29000.00
Timur Kim	1	15700.00
Aliya Bekova	1	25800.00
Ruslan Ivanov	1	9600.00

Aigerim Seitova

orders: 3
total: 58200.00

Daniyar Akhmetov

orders: 1
total: 13000.00

Madina Omarova

orders: 2
total: 29000.00

Timur Kim

orders: 1
total: 15700.00

Aliya Bekova

orders: 1
total: 25800.00

Ruslan Ivanov

orders: 1
total: 9600.00

count(DISTINCT o.id) counts orders. Daniyar has 1, not 2: order 10 has no lines and dropped out in the INNER JOIN with order_items.

Note the second detail in the result: the inner join with order lines dropped the empty order 10. If empty orders matter for the report, use LEFT JOIN and coalesce for the sum.

The other common mistake is count(*) after LEFT JOIN. A client without orders still has one row in the result (with NULL in the order columns), and count(*) counts it. Count a column of the right table instead:

SQL

SELECT c.name, count(o.id) AS orders, count(*) AS rows_in_group
FROM clients AS c
LEFT JOIN orders AS o ON o.client_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

name	orders	rows_in_group
Aigerim Seitova	3	3
Daniyar Akhmetov	2	2
Madina Omarova	2	2
Timur Kim	1	1
Aliya Bekova	1	1
Ruslan Ivanov	1	1
Zarina Kaliyeva	0	1
Sergey Pak	0	1

Aigerim Seitova

orders: 3
rows_in_group: 3

Daniyar Akhmetov

orders: 2
rows_in_group: 2

Madina Omarova

orders: 2
rows_in_group: 2

Timur Kim

orders: 1
rows_in_group: 1

Aliya Bekova

orders: 1
rows_in_group: 1

Ruslan Ivanov

orders: 1
rows_in_group: 1

Zarina Kaliyeva

orders: 0
rows_in_group: 1

Sergey Pak

orders: 0
rows_in_group: 1

Clients without orders: count(o.id) = 0, while count(*) = 1.

Symptom	Cause	Fix
A sum or count is larger than expected	A one-to-many `JOIN` multiplied the rows	`count(DISTINCT …)`, or aggregate first in `WITH` and join afterwards
Clients without orders vanished from the report	`INNER JOIN` instead of `LEFT JOIN`	`LEFT JOIN` from the table whose rows you need in full
`LEFT JOIN` behaves like `INNER`	A condition on the right table sits in `WHERE`	Move the condition into `ON`
A client with no orders shows 1 order	`count(*)` after `LEFT JOIN`	`count(right-table column)`
A total is empty instead of 0	`sum` over an empty set is `NULL`	`coalesce(sum(…), 0)`
`must appear in the GROUP BY clause`	A non-grouped column in `SELECT`	Add it to `GROUP BY` or wrap it in an aggregate

A sum or count is larger than expected

Cause: A one-to-many JOIN multiplied the rows
Fix: count(DISTINCT …), or aggregate first in WITH and join afterwards

Clients without orders vanished from the report

Cause: INNER JOIN instead of LEFT JOIN
Fix: LEFT JOIN from the table whose rows you need in full

LEFT JOIN behaves like INNER

Cause: A condition on the right table sits in WHERE
Fix: Move the condition into ON

A client with no orders shows 1 order

Cause: count(*) after LEFT JOIN
Fix: count(right-table column)

A total is empty instead of 0

Cause: sum over an empty set is NULL
Fix: coalesce(sum(…), 0)

must appear in the GROUP BY clause

Cause: A non-grouped column in SELECT
Fix: Add it to GROUP BY or wrap it in an aggregate

Exercises: revenue by product and clients without orders

The exercises run on the same training database. Revenue is the sum of quantity * price over orders with status paid or shipped. The answers were checked on PostgreSQL 17.11; the expected output is in the comments of each solution.

Repeat1. Revenue by product for September

Build a "revenue by product for September 2026" report: product name, units sold and revenue, highest revenue first. The revenue column must add up to September in the monthly report — 55,500 KZT.

Hint

Join order_items, orders and products; limit the date with a half-open range >= '2026-09-01' AND < '2026-10-01'.

Solution

7 products totalling 55,500 KZT — the same as in the monthly report.

SQL

SELECT p.name,
       sum(oi.quantity)            AS units,
       sum(oi.quantity * oi.price) AS revenue
FROM order_items AS oi
JOIN orders AS o   ON o.id = oi.order_id
JOIN products AS p ON p.id = oi.product_id
WHERE o.status IN ('paid', 'shipped')
  AND o.ordered_on >= '2026-09-01'
  AND o.ordered_on <  '2026-10-01'
GROUP BY p.id, p.name
ORDER BY revenue DESC;

--             name            | units | revenue
-- ----------------------------+-------+----------
--  Espresso Blend 1 kg        |     1 | 12900.00
--  Colombia Supremo 250 g     |     2 |  9800.00
--  French Press 600 ml        |     1 |  8900.00
--  Masala Chai 200 g          |     2 |  7600.00
--  Ceramic Mug 350 ml         |     3 |  7500.00
--  Ethiopia Yirgacheffe 250 g |     1 |  5600.00
--  Green Sencha 100 g         |     1 |  3200.00
-- (7 rows)

Modify2. Clients with no orders in October

Find clients who have no orders in October 2026, though they may have orders in other months. Expect 3 rows.

Hint

If you put the date condition in WHERE, the query returns 0 rows: rows with NULL instead of an order fail the date check. Where should the condition go?

Solution

The month condition goes in ON, the "no match" check in WHERE. Timur Kim ordered only in September; Zarina and Sergey never ordered.

SQL

SELECT c.name
FROM clients AS c
LEFT JOIN orders AS o
       ON o.client_id = c.id
      AND o.ordered_on >= '2026-10-01'
      AND o.ordered_on <  '2026-11-01'
WHERE o.id IS NULL
ORDER BY c.id;

--       name
-- -----------------
--  Timur Kim
--  Zarina Kaliyeva
--  Sergey Pak
-- (3 rows)

Repeat3. Clients by city

Count clients in each city. Show (not set) instead of NULL. Expect 5 rows.

Solution

Group by the original city column; the label comes from coalesce in the select list.

SQL

SELECT coalesce(city, '(not set)') AS city_name, count(*) AS clients
FROM clients
GROUP BY city
ORDER BY clients DESC, city_name;

--  city_name | clients
-- -----------+---------
--  Almaty    |       3
--  Astana    |       2
--  (not set) |       1
--  Karaganda |       1
--  Shymkent  |       1
-- (5 rows)

Modify4. Large categories

Change the revenue-by-category report so that only categories with revenue above 20,000 KZT remain. Expect 2 rows.

Hint

A condition on the sum is a condition on a group.

Solution

HAVING with an aggregate; you cannot reference the revenue alias in HAVING — repeat the expression.

SQL

SELECT p.category, sum(oi.quantity * oi.price) AS revenue
FROM order_items AS oi
JOIN orders AS o   ON o.id = oi.order_id
JOIN products AS p ON p.id = oi.product_id
WHERE o.status IN ('paid', 'shipped')
GROUP BY p.category
HAVING sum(oi.quantity * oi.price) > 20000
ORDER BY revenue DESC;

--  category  | revenue
-- -----------+----------
--  coffee    | 77500.00
--  equipment | 27400.00
-- (2 rows)

Apply5. Average order value by month

For each month compute the number of orders, revenue and the average order value. Only paid and shipped.

Hint

The average order value is not avg(quantity * price) over lines — that is the average line, not the average order. Compute order totals in WITH first.

Solution

September: 4 orders, 55,500 KZT, average 13,875 KZT. October: 3 orders, 67,700 KZT, average 22,566.67 KZT.

SQL

WITH order_totals AS (
    SELECT o.id, o.ordered_on, sum(oi.quantity * oi.price) AS total
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.id
    WHERE o.status IN ('paid', 'shipped')
    GROUP BY o.id, o.ordered_on
)
SELECT date_trunc('month', ordered_on)::date AS month,
       count(*)               AS orders,
       sum(total)             AS revenue,
       round(avg(total), 2)   AS avg_check
FROM order_totals
GROUP BY month
ORDER BY month;

--    month    | orders | revenue  | avg_check
-- ------------+--------+----------+-----------
--  2026-09-01 |      4 | 55500.00 |  13875.00
--  2026-10-01 |      3 | 67700.00 |  22566.67
-- (2 rows)

Apply6. Products with no sales in September

Find products that were not sold in September 2026 (only paid and shipped count). Expect 2 rows.

Hint

In September the Hand Grinder appeared only in a cancelled order. NOT EXISTS with a subquery that joins lines to orders is the easiest route.

Solution

The Hand Grinder is in the result because its September order was cancelled; the Gift Box was never sold.

SQL

SELECT p.name
FROM products AS p
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items AS oi
    JOIN orders AS o ON o.id = oi.order_id
    WHERE oi.product_id = p.id
      AND o.status IN ('paid', 'shipped')
      AND o.ordered_on >= '2026-09-01'
      AND o.ordered_on <  '2026-10-01'
)
ORDER BY p.id;

--      name
-- --------------
--  Hand Grinder
--  Gift Box
-- (2 rows)

Next in the path: running PostgreSQL for your own project and schema design — how to choose tables and links so that reports like these stay simple.

How this was verified4 · passedchecked 11 October 2026

“Run” means the code was executed in a scratch project or container and the output in the article comes from that run. “Partial” means it was checked against stubs instead of external services. “Not run” means it needs a device, an account or a key; the steps were checked against the official documentation. The verification project files and run logs are kept in the InterLAB archive.

SQL: JOIN, GROUP BY and aggregates

Why JOIN: the data lives in different tables

INNER JOIN and LEFT JOIN, side by side

Aggregates: COUNT, SUM, AVG, MIN, MAX and NULL

GROUP BY and HAVING

Subqueries and CTEs (WITH)

Common mistakes: duplicates after JOIN and COUNT(*) vs COUNT(column)

Exercises: revenue by product and clients without orders

Sources

Web Application Development

SQL from scratch: SELECT, WHERE, ORDER BY

Databases: tables, keys and why a business needs them

Database design: relationships, keys and normalisation

PostgreSQL: running in Docker, psql and a first table

SQL reference: queries, data changes, DDL and transactions

Got a project?
We'll scope and estimate it — for free.

01Why JOIN: the data lives in different tables

02INNER JOIN and LEFT JOIN, side by side

03Aggregates: COUNT, SUM, AVG, MIN, MAX and NULL

04GROUP BY and HAVING

05Subqueries and CTEs (WITH)

06Common mistakes: duplicates after JOIN and COUNT(*) vs COUNT(column)

07Exercises: revenue by product and clients without orders

Sources

Web Application Development

SQL from scratch: SELECT, WHERE, ORDER BY

Databases: tables, keys and why a business needs them

Database design: relationships, keys and normalisation

PostgreSQL: running in Docker, psql and a first table

SQL reference: queries, data changes, DDL and transactions

Got a project?We'll scope and estimate it — for free.

Why JOIN: the data lives in different tables

INNER JOIN and LEFT JOIN, side by side

Aggregates: COUNT, SUM, AVG, MIN, MAX and NULL

GROUP BY and HAVING

Subqueries and CTEs (WITH)

Common mistakes: duplicates after JOIN and COUNT(*) vs COUNT(column)

Exercises: revenue by product and clients without orders

Got a project?
We'll scope and estimate it — for free.