OK.
So next up we have another nice clean table just three results.
And as you can see what we're doing here is grabbing things based off of genre.
So we only have three.
Fortunately animation comedy and drama.
And then what we're doing is computing the average review the average rating for each of those genres.
So this will be a little similar to what we've already done earlier and this problem said the main difference
is that we're working with genre now rather than just the title of a given series.
OK.
One thing I def need to correct the record here called this challenge five.
I was wrong.
After four after three comes four now we're on Challenge five.
OK.
And we'll just call this genre average ratings.
OK.
So to get this information from our three tables the two that were that we need to identify that we're
working with are the series table because that's where our genre information is stored.
And then the reviews table that where ratings information is stored.
So we don't care about the reviewers the people who review them.
That doesn't matter.
All we need is genres.
And then the ratings so we can start with either one.
Let's start with Star from series just like that.
OK.
So we're going to want to work with is the genre portion but we're going to need to join with the reviews
table so we'll just do that now.
So let's start from series.
Join revues.
Hopefully this seems familiar now.
On series ID equals revues dot series ID and if we leave a star in there we're going to get a lot of
stuff so we don't need all that.
Let's just pick out a few things like title Johnna and rating.
OK.
So now we can see we've got you know all the ratings for Archer all animation of course because they're
the same exact row here with a relevant rating appended So join them together.
It's an inner join and that's what we want.
We don't want to be working with you know non-existent ratings.
We don't want to take let's say What was it.
Pushing Daisies which didn't have any reviews Well that's a comedy I believe.
Maybe it's a draw.
I don't remember.
But whatever it is we don't want to try an average No into the average rating for dramas so the average
rating for comedy.
So we don't want to do a left join you on.
OK.
So we've got this on Sirius ID.
Blah blah blah blah blah.
Now all we need to do is group things by genre.
That's what we're trying to do here rather than by title group by genre
and if we do that we should see this is immediately reduced down to three rows.
Although remember that it's just kind of hiding all the data that lies beneath.
So we have animation here but that doesn't mean that there's only one.
It's just that gripped by when you when you use a group by it's displayed in a very weird awkward way
that I think is kind of confusing but what we want to do is rather than we can get rid of the title
to it rather than just genre rating.
We just want average rating and that should do it for us and we get the average ratings.
There's an alias so I can add that in like that and that's it.
So now we get animation.
It's the lowest rated still quite high comedy is the highest rated 8.1.
And drama is right in the middle.
8.0.
And I suppose this is as good a time as any to introduce the round function.
We haven't used it yet but we're getting a lot of decimals here and we don't we don't care about them
let's just round it to two decimal places.
So it looks like this round and I'll do it on separate lines here.
So we have round average rating but we don't just say round average rating we have to supply what we
want around to two digits for us and then we'll use ads for trading.
So I could have done it all in one line in gigantic gross line in my opinion.
So it's a little better to break it up like this takes up more space but it's a price we pay.
OK.
So around the average two digits and call it average rating.
Now let's double check that works.
And there we go much easier.
You didn't have to do that of course but it figured it was a good thing to show.