ROLLUP for subtotals in MySQL - Mini Project: Build & Apply

Using ROLLUP for Subtotals in MySQL

📖 Scenario: You work in a retail company database. You want to see total sales by category and product, including subtotals for each category and a grand total.

🎯 Goal: Create a SQL query using ROLLUP to get sales subtotals by category and product.

📋 What You'll Learn

Create a table called sales with columns category (VARCHAR), product (VARCHAR), and amount (INT).

Insert the exact rows: ('Electronics', 'TV', 100), ('Electronics', 'Radio', 50), ('Clothing', 'Shirt', 40), ('Clothing', 'Pants', 60).

Write a SELECT query that sums amount grouped by category and product using GROUP BY ROLLUP(category, product).

Ensure the query returns subtotals per category and a grand total.

💡 Why This Matters

🌍 Real World

Retail and sales databases often need subtotals and grand totals for reports. ROLLUP helps generate these summaries easily.

💼 Career

Knowing how to use ROLLUP is useful for data analysts and database developers to create clear summary reports without complex queries.

1

Create the sales table and insert data

Create a table called sales with columns category as VARCHAR(20), product as VARCHAR(20), and amount as INT. Then insert these exact rows: ('Electronics', 'TV', 100), ('Electronics', 'Radio', 50), ('Clothing', 'Shirt', 40), ('Clothing', 'Pants', 60).

MySQL

-- Create table sales and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Set up the SELECT statement with grouping

Write a SELECT statement that selects category, product, and the sum of amount as total_sales from the sales table. Add a GROUP BY clause grouping by category and product. Do not use ROLLUP yet.

MySQL

SELECT category, product, SUM(amount) AS total_sales
FROM sales
GROUP BY category, product
-- Your code here

Need a hint?

Use SUM(amount) AS total_sales and group by both category and product.

3

Add ROLLUP to get subtotals

Modify the GROUP BY clause to use ROLLUP(category, product) to get subtotals for each category and a grand total.

MySQL

SELECT category, product, SUM(amount) AS total_sales
FROM sales
GROUP BY ROLLUP(category, product)
-- Your code here

Need a hint?

Replace GROUP BY category, product with GROUP BY ROLLUP(category, product).

4

Complete query with ordering for clarity

Add an ORDER BY clause to order results by category ascending and product ascending to make subtotals clear.

MySQL

SELECT category, product, SUM(amount) AS total_sales
FROM sales
GROUP BY ROLLUP(category, product)
ORDER BY category ASC, product ASC
-- Your code here

Need a hint?

Use ORDER BY category ASC, product ASC after the GROUP BY ROLLUP clause.