Lecture Notes: The Rollup Clause in SQL

Overview

• Rollup Clause: An extension of the GROUP BY clause in SQL.
  • Produces another row showing the grand total, known as a super aggregate value.
  • Useful for summarizing grouped data.

Example 1: Transactions Table

• Goal: Group transactions by order date and produce a grand total.
• Steps:
  1. Select the sum of amounts and order date from the transactions table.
  2. Group by the order date.
  3. Add WITH ROLLUP after the GROUP BY clause.
• Result:
  • Shows the sum of amounts per order date.
  • Includes a grand total row where total = $25.22.

Example 2: Counting Transaction IDs

• Goal: Count transaction IDs by order date.
• Findings:
  • 2 transactions on the first date, 2 on the second, 3 on the third.
  • Total of 7 transactions.

Example 3: Transactions by Customer

• Goal: Count transaction IDs per customer.
• Steps:
  1. Select the count of transaction IDs giving an alias as "number of orders."
  2. Include the customer ID column.
  3. Group by customer ID and add WITH ROLLUP.
• Result:
  • Displays number of orders per customer ID.
  • Includes a grand total of 7 orders.

Example 4: Employees Table

• Goal: Calculate hourly pay per employee and a grand total.
• Steps:
  1. Select the sum of hourly pay with an alias "hourly pay."
  2. Display employee ID as a column from the employees table.
  3. Group by employee ID with WITH ROLLUP.
• Result:
  • Displays each employee's hourly pay.
  • Includes a grand total of $92.75 per hour.

Summary

• Rollup Clause:
  • Extension of the GROUP BY clause.
  • Adds a grand total row or super aggregate value.
  • Syntax: Add WITH ROLLUP after GROUP BY.
  • Useful in accounting scenarios to summarize data.

---

Note: Examples given use MySQL syntax.