Examples of Grouping Data with ROLLUP

Examples 1: Total Sales Calculation

Let’s consider a table named sales with the columns region, product, and sales_amount. We want to calculate the total sales amount for each region and product as well as the total sales amount.

Table:

CREATE TABLE sales2 (
    region VARCHAR(50),
    product VARCHAR(50),
    sales_amount INT
);
INSERT INTO sales2 (region, product, sales_amount) VALUES
('East', 'Product A', 5000),
('East', 'Product A', 5000),
('East', 'Product B', 7500),
('East', 'Product B', 7500),
('West', 'Product A', 6000),
('West', 'Product A', 6000),
('West', 'Product B', 6500),
('West', 'Product B', 6500);

The sales2 table now contains the following data:

| region | product   | sales_amount |
|--------|-----------|--------------|
| East   | Product A | 5000         |
| East   | Product A | 5000         |
| East   | Product B | 7500         |
| East   | Product B | 7500         |
| West   | Product A | 6000         |
| West   | Product A | 6000         |
| West   | Product B | 6500         |
| West   | Product B | 6500         |

Query:

SELECT region, product, SUM(sales_amount) AS total_sales
FROM sales2
GROUP BY region, product WITH ROLLUP;

Output:

| region | product   | total_sales |
|--------|-----------|-------------|
| East   | Product A | 10000       |
| East   | Product B | 15000       |
| West   | Product A | 12000       |
| West   | Product B | 13000       |
| East   | NULL      | 25000       |
| West   | NULL      | 25000       |
| NULL   | NULL      | 50000       |

Explanation: This query selects the total sales amount grouped by region and product from the ‘sales2’ table. The WITH ROLLUP modifier adds subtotal rows for each region and a total row with NULL values for both region and product.

Example 2: Total Sales by Category

Let’s analyze another scenario with the table named orders containing columns category, quantity, and price. We want to determine the total sales amount for each category including the total.

orders1 Table:

CREATE TABLE orders1 (
    category VARCHAR(50),
    quantity INT,
    price INT
);
INSERT INTO orders1 (category, quantity, price) VALUES
('Electronics', 2, 5000),
('Electronics', 3, 7000),
('Clothing', 4, 3000),
('Clothing', 2, 2000);

Query:

SELECT category, SUM(quantity * price) AS total_sales
FROM orders1
GROUP BY category WITH ROLLUP;

Output:

| category   | total_sales |
|------------|-------------|
| Electronics| 29000       |
| Clothing   | 16000       |
| NULL       | 45000       |

Explanation: The query calculates the total sales amount for each category by multiplying the quantity by the price and then summing them up. The WITH ROLLUP modifier adds subtotal rows for each category and a total row with NULL as the category.

Grouping data is a common operation in SQL when you want to aggregate data based on certain criteria. The MySQL provides the ROLLUP extension to the GROUP BY clause which allows you to generate subtotals and totals for the groups of rows. This article will give an overview of using the ROLLUP extension in MySQL to group data and calculate subtotals and totals.