Okay.
Moving on to our next little challenge here.
So here's what the end result should look like.
We have two fields just like we did in the previous one, but there's quite a bit of difference.
If you notice, we have titles still, so that's the same, but we only have one entry per series,
so we don't have.
Archer, Archer.
Archer.
We just have Archer once somewhere.
Where are you?
Over here.
But we also have average rating.
So this is for all the reviews, all the ratings for a given title, put them all together and find
the average.
And then the other thing is that the results are then ordered from lowest average to highest.
So basically we're going to work with very similar data, take it same approach, join the same two
tables.
We're joining series with reviews, but then we're going to group them together and then we're going
to average the rating and then we're going to order by average rating.
And just like the previous problem, there are certain series that don't have reviews.
They are not showing up here, so we don't need to worry about that.
If we're worrying about nulls or non-existent data, that's not a problem for this particular challenge.
So we're going to call this challenge.
Two average rating.
So if we just actually type a very similar thing.
So select title and rating from series.
Join reviews on series dot ID equals reviews dot series ID.
This is going to get us really close.
We have all the data we need right here.
This is it.
We don't need IDs or we don't need to worry about release years or genres or anything.
This is all we need.
However, we need to group things together so that all Archer reviews are together.
All Arrested Development reviews are together and so on.
So then that begs the question, how do we group them?
What's the best way?
And there's a couple of approaches that really we could do it based off of the name the title.
But that assumes that there's only one show named Archer, one show named Bob's Burgers, which is probably
true 99.9% of the time.
But it's possible that we have shows with the same name, or maybe there's a remake of a show.
I'm trying to think of.
I know there's been examples of this, whether it's an older show that's been remade more recently,
but basically it's best to not have to worry about that because we're not making we're not enforcing
that.
The name of the show is unique.
If we go to series, the only thing that has to be unique is ID, so it's much better to group based
off of that ID and of course it's here.
Well, we're not seeing it printed out, but we can easily see it if we just add in series ID.
So now if we run this.
You can see we have that ID there.
So even if we had another show named Archer down at the bottom with ID of 15, if we group by the series
ID, then they'll be unique and they won't muddy the averages.
They won't muddy the group by.
So that should work fine.
And we don't actually need that series ID anymore, but we're going to do a group by and we need to
do series ID that's important.
If we just do ID, that is confusing because it could also be refused at ID just to showcase that if
we just select star and I'll get rid of the group by.
If we just like Star, you can see, even though it's still messy.
We've got one ID here and we've got another ID here.
So this is the series ID and this here is the reviews ID.
So we need to be explicit.
So let's undo our changes.
Go back to what we had.
We're going to group by series dot ID, and if we just do that, it doesn't get us that far.
It gets us closer.
But rating is just going to be basically the first one luck of the draw.
Essentially, it's not an average.
It's just showing us one rating.
Even though they're grouped by this ID here, we're still not getting the average.
And that's a very simple thing to change.
You just need to do average here, which we should be comfortable with by now.
And let's give it an alias average rating just like that.
Okay.
Let's take a look.
Looking good.
We've got title and average rating.
A couple of things.
One, we need to get rid of ID, and two, we need to change the order.
So let's start with a simple one.
Get rid of the ID then let's go ahead and at the end, add an order by.
So how do we want to order them?
Well, what about just using average rating?
So order by average rating.
And then what order do we want?
We can work with the default ascending order.
Because that's what I have here from 5.38 up to 9.9.
And there we go, 5.38, up to 9.9.
Now, that's basically the end.
But I do want to highlight that these reviews are a little bit accurate, in my opinion, at least.
General Hospital, not my favorite show.
It only gets 5.3 on average.
Probably well deserved.
I'm sorry if that's a show close to your heart.
But I want to highlight amazing show that nobody watches called Halt and Catch Fire.
I mean, look at this has a 9.9.
How did that even happen?
I mean, who decided that?
That's real data coming from real people.
Millions of ratings.
And the average is 9.9.
Fantastic.
Okay.
But really, this is a great show.
Nobody watches it.
I highly encourage you to watch it, especially if you have any interest in computers.
The history of computers.
History of the Internet.
Although I will say season one is a little slow, but hang in there if you do want to watch it.
All right.
So we're moving on to our next problem in the next video.
Watch, halt and catch fire.