Schema and sample data
| product_id | name | category | price |
|---|---|---|---|
| 1 | Wireless Mouse | Electronics | 25.00 |
| 2 | Mechanical Keyboard | Electronics | 80.00 |
| 3 | SQL Cookbook | Books | 40.00 |
| 4 | Python Basics | Books | 30.00 |
| 5 | Desk Lamp | Home | 45.00 |
| 6 | Coffee Mug | Home | 12.00 |
| order_item_id | product_id | quantity |
|---|---|---|
| 1 | 1 | 3 |
| 2 | 2 | 2 |
| 3 | 3 | 5 |
| 4 | 4 | 4 |
| 5 | 5 | 2 |
| 6 | 6 | 6 |
| 7 | 1 | 2 |
Show setup SQL (copy to run)
CREATE TABLE Products (
product_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL
);
CREATE TABLE OrderItems (
order_item_id INT PRIMARY KEY,
product_id INT NOT NULL,
quantity INT NOT NULL
);
INSERT INTO Products (product_id, name, category, price) VALUES
(1, 'Wireless Mouse', 'Electronics', 25.00),
(2, 'Mechanical Keyboard', 'Electronics', 80.00),
(3, 'SQL Cookbook', 'Books', 40.00),
(4, 'Python Basics', 'Books', 30.00),
(5, 'Desk Lamp', 'Home', 45.00),
(6, 'Coffee Mug', 'Home', 12.00);
INSERT INTO OrderItems (order_item_id, product_id, quantity) VALUES
(1, 1, 3),
(2, 2, 2),
(3, 3, 5),
(4, 4, 4),
(5, 5, 2),
(6, 6, 6),
(7, 1, 2);You have a Products table and an OrderItems table. Each row in OrderItems records how many units of a product were ordered. Return each product category together with its total revenue, where revenue for a line is price * quantity. Alias the summed column revenue. Sort the result by revenue from highest to lowest.
Expected result
| category | revenue |
|---|---|
| Books | 320.00 |
| Electronics | 285.00 |
| Home | 162.00 |
Show hint
Join OrderItems to Products on product_id so each ordered line knows its price and category. Then GROUP BY category and sum the per line product price * quantity. Multiply first, then aggregate: SUM(price * quantity), not SUM(price) * SUM(quantity).
Try to write the query yourself before you open this.
Show solution and explanation
SELECT p.category, SUM(p.price * oi.quantity) AS revenue
FROM OrderItems AS oi
JOIN Products AS p ON p.product_id = oi.product_id
GROUP BY p.category
ORDER BY revenue DESC;The JOIN matches every row in OrderItems to its product, so each ordered line gains a price and a category. Multiplying price * quantity gives the revenue for that single line before anything is grouped. For example, product 1 (25.00) appears twice: 25.00 * 3 and 25.00 * 2, contributing 75.00 and 50.00 to Electronics.
GROUP BY p.category then collapses the joined rows into one row per category, and SUM(p.price * oi.quantity) adds up the line revenues within each group. Electronics totals 75 + 160 + 50 = 285.00, Books totals 200 + 120 = 320.00, and Home totals 90 + 72 = 162.00. See SUM for how the aggregate folds the multiplied values together.
The most common mistake is aggregating each column separately, as in SUM(price) * SUM(quantity). That multiplies category wide totals and inflates the answer, because it pairs every price with every quantity instead of keeping each line intact. Always multiply inside the SUM. Note also that ORDER BY revenue DESC can reference the alias because ORDER BY runs after the SELECT list is projected.