Next up, some other clauses we can use that work with group buy.
And the first of those is having the having clause is used to filter the groups that we get back from
group buy.
So let me show you an example.
Let's just work with this view we made earlier full reviews.
Select star from this view rather select star from full reviews.
And if you didn't watch that video on views, this is the query.
So it's just that join between reviews, series and reviewers.
Anyway, I have this data here and let's do a simple group by lets group by title.
So select start from full reviews group by title.
And what I want to find is the average rating for each title.
So I'll do title comma.
Average rating.
So there we are.
I'm getting Archer Average rating or development?
Average rating.
And this is nothing new, but this is what's new.
What if I want to limit this to only the movies that have more than one review?
At the moment, I don't have control over that.
I can't use a where clause because the where clause wouldn't help me here.
Right?
I need to be able to filter the groups that have been formed.
Group B makes those groups.
So this is what we had before and it's going through in grouping Archer.
Okay, here's a row.
Archer is the title.
Here's another row.
Archer is the title.
Here's another row.
Archer is the title, but I don't currently have a way of saying, but only include the groups that
have more than one row or only include the groups that have a, I don't know, an average of over five
or over seven or something like that.
There's a lot of different filtering that I could perform, but the point is I don't have a way of doing
it right now, but fortunately we have the having clause.
This is what the docs say about having the having clause specifies conditions on groups typically formed
by the group by clause.
So this allows me to narrow down which groups we actually include in our results.
And here is how we do it.
I'm going to put this in a file so we have a record of it right here.
So instead of just selecting title, an average rating from full reviews grouped by title, I'm going
to say grouped by title having and then what is my condition?
I want to make sure that we have a count of rating that is greater than, let's say, more than one
review or more than one rating.
So there's a lot of things I could do here.
I could say only do the groups where title is Archer, but that doesn't really make sense.
Why would I do that?
I can do it.
I only get the group where title is Archer.
Silly but totally valid.
So I'm going to do where the count of rating is greater than one.
So go through full reviews and group every row together by title, but I only care about the ones that
have a count of rating greater than one.
Once you've done that, give me the title and the average rating, we run it.
And did we lose any?
I'm not even sure if we had any.
Let's see.
Let's get rid of the having portion.
Yes.
Halt and catch fire.
I think that the only one we lost anyway.
We lost some, right?
Seinfeld was at the end.
Now we have Seinfeld and Halt and Catch Fire.
And you know how it could make this more obvious is by also including a count of rating.
Let's call this as review count.
Sure.
Okay.
So if I do that now, we can see that all of these have a review count of five, two, four, five,
three, two.
And if I didn't have this having on there, we included Halt and Catch Fire that had one review.
So that's the concept of having it allows us to filter our groups that we are creating with group by
and control the output, control the results that we get back.
So it's different than where where allows us to control what we're selecting to begin with.
But then after we've grouped things, that is where we use having to control what groups we include.