Next up, we'll see another clause we can use with group by called with Rollup.
Now, this one is a little tricky to put into words.
I'll show you what the docs say.
It says that the group by clause permits a width rollup modifier that causes summary output to include
extra rows that represent higher level.
That is super aggregate summary operations.
That sounds a little intense, but if you see an example, I think it helps.
It's not exactly self explanatory, but it helps to explain it quite a bit compared to just reading
the docs.
So let's work again with our what do we have?
Select start from full reviews.
This view that we made this joined three tables join together and let's do a simple thing like use one
of the aggregate functions average or count.
So we know that we can do things like select average rating from full reviews.
Now this gives me one average for the whole table.
But we also know we can do things like select average rating.
If we group by let's do title title.
Average rating from full reviews grouped by and then title.
So it's the the exact same average function, but it's operating at different levels.
In this level, it's operating on groups of rows grouped by title.
At this level, it's operating on all of the rows for the whole table.
Now, when I take this second query that we just ran, this one here, select title and average rating
from full reviews grouped by title and I add with roll up at the end of the group VI.
This only works with group by group by title with rollup.
The results look very similar except for this last row.
This last row here is a summary statistic.
It's a summary average for the entire table.
So we have the average for each group.
Archer Group average is 8.1 to Bob's Burgers, seven five to Stranger Things.
And then this has no title, hence the null.
It is the average for the entire table.
All of the rows 8.02553.
So we could do the same thing for count.
Let me do it in a file though.
So we have title average rating from full reviews grouped by title with Roll up.
Let's do another example.
This time I'll do count.
How many ratings does each one have?
And once again, we're grouping by title.
We see Archer has five.
Fargo has two reviews, Stranger Things has three, and then Noel has 47.
That's all of our counts or the count of all of the ratings in the table.
So this is one level of width rollup.
But what gets more confusing to explain is if we group by multiple columns with rollup also will give
us data at different levels of that hierarchy.
So let me just show an example.
Instead of grouping by title and looking at the title and the count.
Let's do something like Select, Let's group by the year and look at the average rating by year.
So select released year and average rating by year.
So from full reviews.
Group by animal group by the released year.
And I won't do a roll up just yet.
Okay, So we get the average rating for all of the reviews in a given year.
We grouped by year.
Now, if I put that with roll up in there.
Where are we going to get the overall average for all of the ratings?
We've seen this before.
8.2553.
But now, instead of just grouping by release here, I'm going to add on genre.
So let's also display genre.
There we are.
And then the average rating.
With roll up and the results.
It's a little different.
Let me do it without the roll up first.
Just so you see what our end result looks like.
So we're looking at in 2009, all of the animations released animation movies had this average rating
in 1999 comedy.
Okay, so I'm going to run this and we get something quite a bit different.
What we see here is a lot more NOLs.
Each one of these NOLs represents one of those summary statistics, a summary average.
So down at the bottom we have no NOL 8.2553.
That once again is the average for the entire data set, all of our ratings.
But if we start at the top, we have the average rating for dramas released in 1963 and then below that
we have the average rating from all genres in 1963 where there is no genre, it's not grouped by genre,
hence the null.
So we get this second level of summary statistics.
Now, our data is not robust enough to really have a lot of examples until we get down to 2014 right
here, what do we have?
We have animation in 2014, the average review 7.9 for drama in 2014, the average review 9.56.
And then we have the summary for all of 2014, 8.55.
And I mean that tracks rate 9.9 versus 9.5.
You average them, it's going to be around 8.5.
So this shows us how it's working.
It's hard to understand without an example to look at.
But we have our group by Right.
Look at the query one more time here, grouped by release year and genre with Roll up the With Roll
Up gives us not just the average rating for each release year genre combination, but also for each
release year and for all rows.
So that is a brief intro to with Roll Up.
There's more that you can do with it.
There's some operations where you could replace the null and change that to be something else.
There's a special function called grouping, but I just wanted to keep it brief, show you the concept
of with roll up, it helps us generate higher level summary statistics and we're not limited to just
getting one summary statistic.
Every column that we add in here, we don't really have anything else.
I mean, I guess I guess I could do.
I don't know first name of the reviewer, but this makes no sense to do.
But I just want to show that if I did that.
We get a lot more NOLs.
And now we have summary statistics.
We have the average for Colts.
In 1963, drama movies was a 4.5.
But then here we have a summary for all users in 1963 and I said five, but it's 1963 dramas.
And then we have all reviews in 1963, regardless of genre.
And then we could keep going and just see more examples of this.
Look at 2014, and then we have the average for Colt and animation.
Kimbra And Animation, but we also have the average for Colt in drama in 2014 and Wyatt in Drama 2014.
And then we also have the average for all reviewers for drama in 2014.
And then finally, the average for all reviews just in 2014, regardless of genre 8.55.
And then down at the very bottom, we have the average for all reviews, regardless of who left it,
what year and what genre, just all of them.
So that's with Rollup.