GROUP BY

01The problem

Harbourline has thousands of shipment records. Your manager wants to know:

"Which customers have shipped the most containers this year?"

You know how to add up containers for the whole company with SUM. Now you need that total for each customer, then the biggest ones at the top.

02The concept

An aggregate on its own gives one answer for the whole table. GROUP BY gives one answer per group: shipments per customer, revenue per month, customers per city.

It works in three steps:

1. Split the rows into groups that share the same value in the GROUP BY column.
2. Summarise each group separately with the aggregate functions.
3. Return one row per group.

The syntax

SELECT group_column, AGGREGATE(column) AS name
FROM table_name
WHERE condition
GROUP BY group_column
ORDER BY ...;

GROUP BY comes after WHERE and before ORDER BY:

SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY ... LIMIT ...

Your first GROUP BY

How many shipments are in each status?

SELECT status, COUNT(*) AS shipments
FROM shipments
GROUP BY status;

Four statuses, four rows. Add more aggregates and each one is worked out per group:

SELECT
  status,
  COUNT(*)        AS shipments,
  SUM(containers) AS containers
FROM shipments
GROUP BY status;

The golden rule

Every column in SELECT must be either:

• listed in GROUP BY, or
• inside an aggregate function.

Why? Each result row represents a whole group. status is the same for every row in its group, so it can be shown. containers differs from row to row, so on its own it has no single value for the group; SUM(containers) does.

This breaks the rule:

SELECT status, shipment_id, COUNT(*)
FROM shipments
GROUP BY status;

There are 2,411 delivered shipments. Which shipment_id should appear on the "Delivered" row? Most databases reject the query. SQLite runs it and shows one arbitrary ID, which looks like an answer but means nothing.

Grouping by a column you summarise elsewhere

Customers per city, largest first:

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
ORDER BY customers DESC;

Average, shortest and longest planned journey for each transport mode:

SELECT
  mode,
  COUNT(*)                          AS routes,
  ROUND(AVG(target_transit_days), 1) AS average_days,
  MIN(target_transit_days)          AS shortest,
  MAX(target_transit_days)          AS longest
FROM routes
GROUP BY mode;

Air routes average under two days; sea routes 23.

Filter rows first, then group

WHERE removes rows before they're grouped, so they never reach any group. This year's busiest customers by containers, excluding cancellations:

SELECT
  customer_id,
  COUNT(*)        AS shipments,
  SUM(containers) AS containers
FROM shipments
WHERE booking_date >= '2026-01-01'
  AND status <> 'Cancelled'
GROUP BY customer_id
ORDER BY containers DESC
LIMIT 10;

The result shows customer IDs rather than names, because names live in the customers table. You'll bring them together in the JOINs lesson.

Grouping by several columns

List more than one column and you get one group for each combination:

SELECT city, industry, COUNT(*) AS customers
FROM customers
GROUP BY city, industry
ORDER BY city, customers DESC;

Each row is one city and industry pair. A pair with no customers simply doesn't appear: GROUP BY only creates groups for values that exist.

Grouping by a calculation

You can group by a calculated value. strftime('%Y-%m', booking_date) turns a date into its year and month, such as '2026-03', which gives monthly figures:

SELECT
  strftime('%Y-%m', booking_date) AS month,
  COUNT(*)                        AS shipments,
  SUM(containers)                 AS containers
FROM shipments
GROUP BY month
ORDER BY month;

Change the format to '%Y' for yearly totals:

SELECT
  strftime('%Y', booking_date) AS year,
  COUNT(*)                     AS shipments
FROM shipments
GROUP BY year;

Groups and NULL

Rows where the grouping column is NULL form one group of their own. Customers per account manager, including the ones with none:

SELECT account_manager_id, COUNT(*) AS customers
FROM customers
GROUP BY account_manager_id
ORDER BY account_manager_id;

The first row, with an empty manager, is the eight customers nobody looks after yet.

Sorting grouped results

ORDER BY runs after grouping, so it can sort by the group column or by an aggregate's alias:

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

Bank transfers bring in by far the most money.

03Example

Kemi wants to know who ships the most this year: the top ten customers by containers.

SELECT
  customer_id,
  COUNT(*)        AS shipments,
  SUM(containers) AS containers
FROM shipments
WHERE booking_date >= '2026-01-01'
GROUP BY customer_id
ORDER BY containers DESC
LIMIT 10;

04Walkthrough

The database works through it in this order:

1. FROM shipments: every shipment.
2. WHERE booking_date >= '2026-01-01': only this year's.
3. GROUP BY customer_id: one group per customer.
4. SELECT: for each group, the customer ID, COUNT(*) of its shipments and SUM(containers).
5. ORDER BY containers DESC: biggest shippers first. containers here is the alias you created.
6. LIMIT 10: the top ten.

Common mistakes

Summary

05Practice

06Challenge

07More practice

Optional drills on the same skills. They don't count towards the certificate, but each one is a small, realistic request from someone at Harbourline. Do as many as you need until the pattern feels automatic.

08Check your understanding