SQL: The Monthly Revenue Report in Five Lines (GROUP BY Month Instead of a Spreadsheet Every Month-End)

Every month-end, the owner of a small business asks the same two questions: how much did we sell, and how did each month do?

In a lot of offices the answer is a spreadsheet. Someone exports the orders, pastes them in, and adds them up by month. It takes most of an hour, and it goes wrong the month somebody misses a row.

If the orders already live in a database, that hour is four short queries. And next month you don't redo anything. You just run them again.

Here's the version from my free lesson, with a small made-up table you can paste into DB Fiddle (free, no account, pick MySQL).

The orders table

Lakeside Office Supply is a made-up shop with eight orders across one quarter. Paste this into the Schema SQL box on the left:

CREATE TABLE orders (
id INT, order_date DATE,
customer VARCHAR(40), amount INT);

INSERT INTO orders VALUES
(1, '2026-07-02', 'Maple Dental', 1250),
(2, '2026-07-15', 'Cobalt Legal', 860),
(3, '2026-07-28', 'Northside Realty', 2140),
(4, '2026-08-06', 'Cobalt Legal', 2310),
(5, '2026-08-19', 'Harbor Accounting', 640),
(6, '2026-08-27', 'Maple Dental', 980),
(7, '2026-09-09', 'Northside Realty', 1890),
(8, '2026-09-22', 'Cobalt Legal', 1270);

Text goes in single quotes, numbers don't, and the last row ends with a semicolon instead of a comma. That semicolon tells the database the statement is done.

Query 1: look at everything

In the Query SQL box on the right:

SELECT * FROM orders;

Click Run. Eight rows: the whole quarter. The star means every column.

Query 2: just one month

SELECT * FROM orders
WHERE order_date >= '2026-09-01';

WHERE keeps only the rows that match. Two September orders.

Query 3: the quarter in one line

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

COUNT(*) counts rows, SUM(amount) adds up the column, and AS names each result column. You get one row: 8 orders, 11340.

Query 4: revenue by month

This is the report the owner actually wants:

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
SUM(amount) AS revenue
FROM orders
GROUP BY month
ORDER BY month;

month	revenue
2026-07	4250
2026-08	3930
2026-09	3160

DATE_FORMAT turns 2026-07-02 into 2026-07, so every July order gets the same label. GROUP BY month puts rows with the same label into one pile, and SUM adds up each pile. If you've used a pivot table, it's a pivot table in one line. ORDER BY month puts the months in date order.

Three things that trip people up

1. Line order matters. It's always SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Put GROUP BY before WHERE and you get a syntax error.
2. DATE_FORMAT is MySQL. In PostgreSQL use TO_CHAR(order_date, 'YYYY-MM'), and in SQLite use strftime('%Y-%m', order_date). The rest of the query is the same.
3. Ask for a read-only login at work. A report only needs SELECT. Nobody should be running their first queries with permission to delete the orders table.

Why this beats the spreadsheet

The same five lines work on 8 orders or 80,000. Nothing gets copied, so nothing gets missed. When October closes, you press Run.

Practice it free

The lesson, SQL: Build a Monthly Revenue Report from an Orders Table, walks you through all four queries in 14 steps on real DB Fiddle screens. You type every line yourself, it checks each step, and it takes about 12 minutes. It's module 1 of the free SQL for Business Reports course. There's also a one-page SQL cheat sheet with all three report queries from the course.

Practice it step by step, free: https://learn.ynteractive.com/content/sql-monthly-revenue-report