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

GROUP BY splits the rows into groups that share a value, then runs your aggregate functions once per group.

Think of it as sorting shipment slips into piles, one pile per customer, then counting the containers in each pile.

Two rules keep you out of trouble:

1. Every column in SELECT must either be in GROUP BY or be inside an aggregate function.
2. GROUP BY comes after WHERE and before ORDER BY.

SELECT … FROM … WHERE … GROUP BY … ORDER BY … LIMIT …

03Example

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

Line by line:

• FROM shipments WHERE booking_date >= '2026-01-01' takes this year's shipments.
• GROUP BY customer_id makes one group per customer.
• COUNT(*) counts the shipments in each group, and SUM(containers) adds up their containers.
• ORDER BY containers DESC puts the biggest shippers first. Here containers refers to the alias you created.
• LIMIT 10 keeps the top ten.

The result has one row per customer, not one per shipment. You'll see customer IDs rather than names, because names live in the customers table. You'll join the two in the JOINs lesson.

You can group by more than one column. This counts customers for each city and industry 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, with the number of customers in it.

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

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

05Practice

06Challenge

07Check your understanding