SQL: Build a Monthly Revenue Report from an Orders Table

Why this matters

Priya (made up) runs the office at Lakeside Office Supply. Every month-end the owner asks: how much did we sell, and how did each month do? The answer lives in the order system, so Priya exports it, pastes it into a spreadsheet and adds it up by hand. It takes most of an hour, and one missed row makes the whole report wrong.

SQL is the language almost every business database understands. Four short queries give the same report straight from the data, in seconds, and next month you just run them again. In this module you type a small orders table into DB Fiddle, a free database in your browser, and write those four queries yourself, one line at a time.

What you’ll build

An orders table with eight Q3 orders, and four queries you typed: every order, September only, the quarter in one line (8 orders, $11,340) and revenue by month.

With the sample data: July $4,250, August $3,930 and September $3,160.

What you need

Nothing to start the practiceThe practice is built from real DB Fiddle screens and runs in this page.
A browser, to do it for realDB Fiddle is free and needs no account. Leave the Database menu on MySQL (the default).
At work: read access to your sales databaseAsk whoever runs your order or accounting system for a read-only login or an export. The queries are the same.

Concepts

3 ideas you’ll use in the practice.

A table and a question

A database keeps data in tables: rows (one per order) and columns (date, customer, amount). In DB Fiddle the left box, Schema SQL, builds the table; the right box, Query SQL, asks the question.

At work the table already exists, filled by your order system, so you only ever write the right-hand part.

SELECT, WHERE and SUM

SELECT * FROM orders shows every column of every row. WHERE keeps only the rows that match, such as order_date >= '2026-09-01'.

COUNT(*) counts rows and SUM(amount) adds up a column. AS gives each result a readable name, such as revenue.

GROUP BY turns rows into a report

GROUP BY puts rows that share a value into one pile and adds each pile up: one row per month instead of one row per order. It is a pivot table written as a few lines you can run again.

DATE_FORMAT(order_date, '%Y-%m') turns 2026-07-02 into 2026-07, so every July order lands in the same pile. Other databases name the date function differently (PostgreSQL: to_char(order_date, 'YYYY-MM')); the rest of the query is the same.

Practice

Do every step yourself in a practice copy of the real tool. It checks your work, and “Do it for me” finishes any step you get stuck on.

DB FiddleSQL: Build a Monthly Revenue Report from an Orders Table14 steps · ~12 min · nothing touches your real accounts

Start the practice →Prefer to read? Step-by-step guide

Do it for real

Now repeat it in your own account. Tick each step as you go.

Open db-fiddle.com. Leave Database on MySQL. No account is needed.
Click in Schema SQL and type the three CREATE TABLE orders lines, then the INSERT INTO orders VALUES line and the eight orders, three lines at a time (the last order ends with a semicolon).
Click in Query SQL, type SELECT * FROM orders; and click Run. Check you see all eight rows.
Select the query (Ctrl+A) and type SELECT * FROM orders, then on the next line WHERE order_date >= '2026-09-01'; and click Run.
Replace it with SELECT COUNT(*) AS orders, then SUM(amount) AS revenue, then FROM orders; and click Run.
Type the monthly report line by line: SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, then SUM(amount) AS revenue, then FROM orders, then GROUP BY month, then ORDER BY month; and click Run.
At work, type the same queries against your own orders table (ask for read-only access) and change the column names to match.

Quick check

3 questions to check you’ve got it.

1. Which line keeps only September's orders?

GROUP BY month
WHERE order_date >= '2026-09-01'
ORDER BY order_date

Show the answer

WHERE order_date >= '2026-09-01'. WHERE filters rows before anything is added up. GROUP BY makes piles and ORDER BY only sorts.

2. What does GROUP BY month do in the last query?

Sorts the orders by month
Makes one row per month and adds up each month
Deletes orders from other months

Show the answer

Makes one row per month and adds up each month. GROUP BY puts all orders with the same month into one row, and SUM(amount) adds them up.

3. With the sample data, which month sold the most?

July, $4,250
August, $3,930
September, $3,160

Show the answer

July, $4,250. July had three orders: $1,250 + $860 + $2,140 = $4,250.

Up next · Module 2

SQL: Top Customers and Key Accounts with GROUP BY and HAVING

Next, the same orders answer a different question: who are your top customers, and which accounts are worth $3,000 or more?

Start module 2 →