ROLLUP for subtotals in MySQL - Time & Space Complexity

Time Complexity: ROLLUP for subtotals

O(n + g)

Understanding Time Complexity

When using ROLLUP in SQL, we want to know how the time to get subtotals grows as the data grows.

We ask: How does adding more rows or grouping levels affect the work done?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT category, subcategory, SUM(sales) AS total_sales
FROM sales_data
GROUP BY category, subcategory WITH ROLLUP;
    

This query groups sales by category and subcategory, then adds subtotal rows for each category and a grand total.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Scanning all rows to aggregate sales.
• How many times: Once over all rows, then grouping and subtotal calculations for each group level.

How Execution Grows With Input

As the number of rows grows, the database scans more data. Also, more groups mean more subtotal calculations.

Input Size (n rows)	Approx. Operations
10	Scan 10 rows + subtotal for groups
100	Scan 100 rows + subtotal for groups
1000	Scan 1000 rows + subtotal for groups

Pattern observation: The scan grows linearly with rows, and subtotal steps grow with number of groups, which depends on distinct categories and subcategories.

Final Time Complexity

Time Complexity: O(n + g)

This means the work grows with the number of rows plus the number of groups for subtotals.

Common Mistake

[X] Wrong: "ROLLUP just adds a fixed small cost regardless of data size."

[OK] Correct: The subtotal calculations depend on how many groups exist, which can grow with data size, so cost is not fixed.

Interview Connect

Understanding how grouping and subtotals affect query time helps you explain performance in real projects and shows you think about data size impact.

Self-Check

"What if we added more grouping columns to the ROLLUP? How would the time complexity change?"

Practice

1. What does the ROLLUP keyword do in a MySQL GROUP BY query?

easy

A. It adds subtotal and total rows automatically in the grouped result.

B. It filters out NULL values from the result set.

C. It sorts the result set in descending order.

D. It creates a backup of the grouped data.

Solution

1. Step 1: Understand GROUP BY basics

   GROUP BY groups rows by column values to aggregate data.

2. Step 2: Role of ROLLUP in GROUP BY

   ROLLUP adds extra rows showing subtotals and grand totals for grouped data.

3. Final Answer:

   It adds subtotal and total rows automatically in the grouped result. -> Option A

4. Quick Check:

   ROLLUP = subtotals and totals [OK]

Hint: ROLLUP adds summary rows automatically [OK]

Common Mistakes:

• Thinking ROLLUP filters data
• Confusing ROLLUP with ORDER BY
• Assuming ROLLUP creates backups

2. Which of the following is the correct syntax to use ROLLUP in a MySQL query?

easy

A. SELECT category, SUM(amount) FROM sales GROUP BY ROLLUP(category);

B. SELECT category, SUM(amount) FROM sales ROLLUP GROUP BY category;

C. SELECT category, SUM(amount) FROM sales GROUP BY category WITH ROLLUP;

D. SELECT category, SUM(amount) FROM sales GROUP BY category ROLLUP;

Solution

1. Step 1: Recall correct ROLLUP syntax

   In MySQL, ROLLUP is used as GROUP BY ... WITH ROLLUP.

2. Step 2: Analyze each option

   SELECT category, SUM(amount) FROM sales GROUP BY category WITH ROLLUP; uses GROUP BY category WITH ROLLUP, which is correct syntax. Others misuse ROLLUP placement or syntax.

3. Final Answer:

   SELECT category, SUM(amount) FROM sales GROUP BY category WITH ROLLUP; -> Option C

4. Quick Check:

   ROLLUP goes after GROUP BY with WITH keyword [OK]

Hint: Use 'GROUP BY ... WITH ROLLUP' syntax [OK]

Common Mistakes:

• Placing ROLLUP before GROUP BY
• Using ROLLUP as a function
• Omitting WITH keyword

3. Given the table sales with columns region, product, and amount, what will be the output of this query?

SELECT region, product, SUM(amount) AS total_sales
FROM sales
GROUP BY region, product WITH ROLLUP;

medium

A. Rows grouped by region and product, plus subtotals by region and a grand total row with NULLs.

B. Only rows grouped by region and product without any subtotal or total rows.

C. Rows grouped by product only, ignoring region.

D. Syntax error due to incorrect use of ROLLUP.

Solution

1. Step 1: Understand GROUP BY with multiple columns and ROLLUP

   ROLLUP creates subtotals for each grouping level: first by region and product, then by region only, then grand total.

2. Step 2: Interpret NULL values in output

   Subtotal rows have NULL in the column(s) rolled up. The grand total row has NULL in both region and product.

3. Final Answer:

   Rows grouped by region and product, plus subtotals by region and a grand total row with NULLs. -> Option A

4. Quick Check:

   ROLLUP adds subtotal and total rows with NULL markers [OK]

Hint: ROLLUP adds subtotal rows with NULLs in rolled-up columns [OK]

Common Mistakes:

• Expecting no subtotal rows
• Confusing grouping levels
• Thinking NULL means missing data

4. You wrote this query but it gives an error:

SELECT region, product, SUM(amount) FROM sales GROUP BY region, product ROLLUP;

What is the error and how to fix it?

medium

A. ROLLUP cannot be used with multiple columns in GROUP BY.

B. GROUP BY clause must come after WHERE clause.

C. SUM() function is not allowed with ROLLUP.

D. ROLLUP must be used with the keyword WITH after GROUP BY.

Solution

1. Step 1: Identify syntax error in ROLLUP usage

   ROLLUP must be used as GROUP BY ... WITH ROLLUP, not just appended after GROUP BY columns.

2. Step 2: Correct the query syntax

   Change query to GROUP BY region, product WITH ROLLUP to fix the error.

3. Final Answer:

   ROLLUP must be used with the keyword WITH after GROUP BY. -> Option D

4. Quick Check:

   ROLLUP requires WITH keyword [OK]

Hint: Use 'WITH ROLLUP' after GROUP BY columns [OK]

Common Mistakes:

• Omitting WITH keyword
• Misplacing ROLLUP before GROUP BY
• Thinking ROLLUP disallows multiple columns

5. You want to get sales totals by region and product, plus subtotals by region and a grand total. Which query correctly uses ROLLUP and filters out only the grand total row from the result?

hard

A. SELECT region, product, SUM(amount) FROM sales GROUP BY region, product WITH ROLLUP HAVING region IS NOT NULL;

B. SELECT region, product, SUM(amount) FROM sales GROUP BY region, product WITH ROLLUP HAVING NOT (region IS NULL AND product IS NULL);

C. SELECT region, product, SUM(amount) FROM sales GROUP BY region, product WITH ROLLUP HAVING product IS NOT NULL;

D. SELECT region, product, SUM(amount) FROM sales GROUP BY region, product WITH ROLLUP HAVING region IS NULL AND product IS NULL;

Solution

1. Step 1: Understand ROLLUP output rows

   ROLLUP adds subtotal rows with NULLs in rolled-up columns; grand total row has NULL in all grouped columns.

2. Step 2: Filter out only the grand total row

   The grand total row has both region and product as NULL. To exclude it, use HAVING NOT (region IS NULL AND product IS NULL).

3. Final Answer:

   SELECT region, product, SUM(amount) FROM sales GROUP BY region, product WITH ROLLUP HAVING NOT (region IS NULL AND product IS NULL); -> Option B

4. Quick Check:

   Exclude grand total by filtering NULLs in all grouping columns [OK]

Hint: Exclude grand total by HAVING NOT all grouping columns NULL [OK]

Common Mistakes:

• Filtering only one column NULL
• Filtering to keep only grand total instead of excluding
• Using WHERE instead of HAVING with GROUP BY