AI Hints
Homeโ€บSQL Tutorialโ€บJoining Many Tables
SQL ยท Lesson 49 of 126

Revenue with Joins

JOIN plus GROUP BY.

โฑ 2 min readโ–ถ Video at 1:09:26๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Here's where everything comes together. Helen's biggest questions are about money. How much did each order earn? Which genre makes the most? But the quantity is in one table, and the price is in another. So we need joins, calculations, and group by, all working together.

Revenue with Joins
From the video: Revenue with Joins
  • How much did each order earn?
  • Quantity in one table, price in another
  • Joins plus GROUP BY

Order totals

A line's value is the quantity times the price. Group by the order, and sum the line values, and you get each order's total. Run it. The biggest order ever was one hundred and sixty five ninety five.

query.sqlsql
SELECT oi.order_id,
       ROUND(SUM(oi.quantity * b.price), 2) AS order_total
FROM order_items AS oi
JOIN books AS b ON b.book_id = oi.book_id
GROUP BY oi.order_id
ORDER BY order_total DESC
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ order_id โ”‚ order_total โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 1372     โ”‚ 165.95      โ”‚
โ”‚ 2003     โ”‚ 135.95      โ”‚
โ”‚ 2159     โ”‚ 132.94      โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Revenue by genre

Now revenue per genre. We join order items to books, for the price and the genre. And to orders, so we can ignore cancelled and returned orders. Then group by genre. Run it. Technology earns the most, at eight thousand six hundred and ten dollars. History earns the least.

query.sqlsql
SELECT b.genre,
       ROUND(SUM(oi.quantity * b.price), 2) AS revenue
FROM order_items AS oi
JOIN books  AS b ON b.book_id  = oi.book_id
JOIN orders AS o ON o.order_id = oi.order_id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY b.genre
ORDER BY revenue DESC;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚   genre    โ”‚ revenue โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Technology โ”‚ 8610.34 โ”‚
โ”‚ Fantasy    โ”‚ 6981.08 โ”‚
โ”‚ Mystery    โ”‚ 6572.01 โ”‚
โ”‚ Thriller   โ”‚ 5674.2  โ”‚
โ”‚ Business   โ”‚ 4310.02 โ”‚
โ”‚ Self-help  โ”‚ 3402.15 โ”‚
โ”‚ Cooking    โ”‚ 3287.15 โ”‚
โ”‚ Romance    โ”‚ 3169.91 โ”‚
โ”‚ History    โ”‚ 2291.88 โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Revenue by year

Same idea, but grouped by year. Run it. About fifteen thousand seven hundred dollars in twenty twenty four. And about twenty eight thousand six hundred in twenty twenty five. Helen's sales nearly doubled.

query.sqlsql
SELECT strftime('%Y', o.order_date) AS year,
       ROUND(SUM(oi.quantity * b.price), 2) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN books       AS b  ON b.book_id   = oi.book_id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY year;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ year โ”‚ revenue  โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 2024 โ”‚ 15693.76 โ”‚
โ”‚ 2025 โ”‚ 28604.98 โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Joins bring the numbers together, and group by adds them up. Joins plus group by answer most business questions. Let's practise.

Key takeaways
  • quantity * price
  • JOIN for the price
  • GROUP BY for the totals
Next up: Practice: Revenue by Country โ€” four tables, one answer