Lessons · SQL · grouping by month across years

Grouping by calendar month

The month number alone is not a period: strftime('%m') puts last March and this March in one bucket. Use '%Y-%m' so each calendar month is its own group.

Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.

What it is for

Monthly revenue, signups per month, tickets per month: the most common chart in any company, and the most common way to get it wrong across a year boundary.

How to think about it

Bucket on the full period key (year and month), sort by it, and check that the number of buckets matches the number of months in the data.

Worked example

SELECT strftime('%Y-%m', placed) AS month, SUM(amount) AS revenue

YYYY-MM: one key per calendar month.

FROM orders

The rows.

GROUP BY month ORDER BY month;

One row per month, in order.

SELECT strftime('%m', placed) AS m, COUNT(*) FROM orders GROUP BY m;

The wrong version: twelve buckets at most, however many years the data spans.

Your turn

Reviews per calendar month.

SELECT strftime('', placed) AS month, COUNT(*) FROM reviews GROUP BY month;

The trap

strftime works on ISO text dates (YYYY-MM-DD). A date stored as 03/04/2026 groups as garbage without an error; check the format before trusting the buckets.

Practise grouping by month across years on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.