Revenue with Joins
JOIN plus GROUP BY.
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.

- 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.
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;โโโโโโโโโโโโฌโโโโโโโโโโโโโโ โ 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.
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;โโโโโโโโโโโโโโฌโโโโโโโโโโ โ 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.
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;โโโโโโโโฌโโโโโโโโโโโ โ 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