Okay.
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 or the average rating for each of those genres.
So this will be a little similar to what we've already done earlier in this problem set.
The main difference is that we're working with genre now rather than just the title of a given series.
One thing I definitely need to correct the record here.
I called this challenge five.
I was wrong.
After four, after three comes four.
Now we're on challenge five
and we'll just call this genre average ratings.
So to get this information from our three tables, the two that we're 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's where our ratings information is stored.
So we don't care about the reviewers, the people who reviewed them.
That doesn't matter.
All we need is the genres and then the ratings, so we can start with either one.
Let's start with Star from series just like that.
Okay.
So all 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 select start from series join reviews.
Hopefully this seems familiar now on series dot ID equals reviews dot series ID and if we leave the
star in there, we're going to get a lot of stuff.
So we don't need all of that.
Let's just pick out a few things like title, genre and rating.
So now we can see we've got all the ratings for Archer, all animation, of course, because they're
the same exact row here with a relevant rating appended.
So we've joined them together.
It's an inner join and that's what we want.
We don't want to be working with 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 drama.
I don't remember.
But whatever it is, we don't want to try and average nul into the average rating for drama.
So the average rating for comedy.
So we don't want to do a left join.
We want inner.
So we've got this on series 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, grouped 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 it doesn't mean that there's only one.
It's just that group 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 then we can get rid of the title two rather than just genre and rating.
We just want average rating.
And that should do it for us.
And we get the average ratings and there's an alias, so you 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 let's do 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 to round it to two digits for us and then we'll use adds average rating.
So I could have done it all in one line, a gigantic gross line in my opinion.
So it's a little better to break it up like this.
It takes up more space, but it's a price we pay.
So round 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 I figured it was a good thing to show.