OK 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 is 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 you know Archer Archer Archer
Archer Archer we just have Archer wants somewhere where are you over here.
But we also have average rating.
So this is for all of 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 and take that 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're not showing up here so we don't need to worry about that.
Well if we're worrying about Knoll's or non-existent data that's not a problem for this particular challenge.
So we're going to call this challenge
to average rating.
So if we just actually type a very similar thing.
So select title and rating from series join previews on series.
ID equals reviews.
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 scares 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
ninety nine point nine percent 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 where there is an older show that's been remade more recently but
basically it's best to not have to worry about it 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 idea 15 if we grouped by the series
ID then they'll be unique and they won't you know Muddy the averages and muddy the group.
So that should work fine.
We don't actually need that series idea anymore but we're going to do a group buy and we need to do
series idea that's important.
If we just do id that is confusing because it could also be reviews that Id just to showcase that if
we just select star I'll get rid of the group by just like star.
You can see even though it's still messy We've got one idea here and we've got another idea here.
So this is the series ID and this here is the reviews ID.
So we need to be explicit.
OK so let's undo our changes.
Go back to what we had.
We're going to group by series ID and if we just do that doesn't get us that far gets us closer but
raiding is just going to be the face for the first one.
Luck of the draw essentially.
It's not an average that's just showing us one rating even though they're grouped by this idea here.
We're still not getting the average and that's a very simple thing to change.
We 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.
OK.
Let's take a look.
Looking good.
We've got title an average rating.
Couple of things.
One we need to get rid of ID and.
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 ordered 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.3 eight up to nine
point nine.
And there we go.
Five point 3:08 up to nine point nine.
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 only gets five point three on average probably well deserved.
I'm sorry if that's shoke 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 nine point nine.
Fantastic.
OK.
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 out and catch fire.