Hey again, it's me. Today I'm going to be explaining the rollup clause, like a fruit rollup. It's an extension of the group by clause. It produces another row and shows the grant total. It's also known as a super aggregate value.
Here's an example. I have a table of transactions. What I would like to do is to group each transaction by the order date, then produce a grant total. Here's what we'll do.
I'll give you a few different examples. Let's begin by selecting the sum of the amounts and the order date from our table transactions then we will group by the order date. And there we are.
We have the sum of every amount per order date. Using this rollup clause, I can add an additional row that shows a grand total, so to say. All I have to do after the group by clause is add with rollup. And there you go.
Here's the grand total. 7.88 plus 8.37 plus 8.97 is $25.22. Here's another example.
Let's count the transaction IDs by order date. We have two transactions on the first, two on the second, three on the third, for a total of seven transactions. I'll give you two more examples.
Let's select the count of our transaction ids per customer. I'm going to give this count an alias as number of orders. As well as the customer ID column from our table transactions, we will group by customer ID. Let's see what we have so far.
Then I will add with rollup. Here are the amount of orders per customer ID. The customer with an ID of 3 has visited here more than once.
The grand total of the number of orders is 7. That's the rollup. One last example. Let's select our Employees table.
Select All from Employees. We will group all of these employees by their employee ID, then display a rollup of all of the hourly pay. As a business, how much are we spending on all of our employees per hour? So let's select the sum of our hourly pay. I'll give this an alias.
As hourly pay. I guess it really isn't different from the column name. We will display the employee ID as a column from our table employees group by the employee ID with rollup. Here's the hourly pay of each employee as well as a grand total. If all of our employees are working currently, we are spending, as a business, $92.75 per hour.
Well, yeah, that's the roll-up clause, everybody. It's an extension of the GROUP BY clause. It produces another row and shows, essentially, the grand total. It's also known as a super aggregate value. To include a rollup, after the GROUP BY clause, just add WITH ROLLUP.
It's probably good for accounting. And well, yeah, that's the rollup clause in MySQL.