1
00:00:00,120 --> 00:00:04,710
Next up, some other clauses we can use that work with group buy.

2
00:00:04,710 --> 00:00:12,900
And the first of those is having the having clause is used to filter the groups that we get back from

3
00:00:12,900 --> 00:00:13,720
group buy.

4
00:00:13,740 --> 00:00:16,470
So let me show you an example.

5
00:00:16,470 --> 00:00:20,460
Let's just work with this view we made earlier full reviews.

6
00:00:20,460 --> 00:00:25,830
Select star from this view rather select star from full reviews.

7
00:00:26,900 --> 00:00:32,450
And if you didn't watch that video on views, this is the query.

8
00:00:32,750 --> 00:00:37,550
So it's just that join between reviews, series and reviewers.

9
00:00:37,940 --> 00:00:43,460
Anyway, I have this data here and let's do a simple group by lets group by title.

10
00:00:44,130 --> 00:00:49,710
So select start from full reviews group by title.

11
00:00:49,710 --> 00:00:54,270
And what I want to find is the average rating for each title.

12
00:00:54,270 --> 00:00:55,780
So I'll do title comma.

13
00:00:55,800 --> 00:00:57,870
Average rating.

14
00:00:59,160 --> 00:00:59,940
So there we are.

15
00:00:59,940 --> 00:01:02,820
I'm getting Archer Average rating or development?

16
00:01:02,820 --> 00:01:03,600
Average rating.

17
00:01:03,600 --> 00:01:06,210
And this is nothing new, but this is what's new.

18
00:01:06,240 --> 00:01:12,540
What if I want to limit this to only the movies that have more than one review?

19
00:01:12,750 --> 00:01:15,270
At the moment, I don't have control over that.

20
00:01:15,780 --> 00:01:21,290
I can't use a where clause because the where clause wouldn't help me here.

21
00:01:21,300 --> 00:01:21,870
Right?

22
00:01:21,930 --> 00:01:25,620
I need to be able to filter the groups that have been formed.

23
00:01:25,950 --> 00:01:27,750
Group B makes those groups.

24
00:01:27,750 --> 00:01:31,420
So this is what we had before and it's going through in grouping Archer.

25
00:01:31,470 --> 00:01:32,550
Okay, here's a row.

26
00:01:32,580 --> 00:01:33,570
Archer is the title.

27
00:01:33,600 --> 00:01:34,380
Here's another row.

28
00:01:34,410 --> 00:01:35,550
Archer is the title.

29
00:01:35,580 --> 00:01:36,510
Here's another row.

30
00:01:36,540 --> 00:01:42,930
Archer is the title, but I don't currently have a way of saying, but only include the groups that

31
00:01:42,930 --> 00:01:49,530
have more than one row or only include the groups that have a, I don't know, an average of over five

32
00:01:49,530 --> 00:01:51,480
or over seven or something like that.

33
00:01:52,170 --> 00:01:57,150
There's a lot of different filtering that I could perform, but the point is I don't have a way of doing

34
00:01:57,150 --> 00:02:01,050
it right now, but fortunately we have the having clause.

35
00:02:01,080 --> 00:02:06,750
This is what the docs say about having the having clause specifies conditions on groups typically formed

36
00:02:06,750 --> 00:02:08,130
by the group by clause.

37
00:02:08,729 --> 00:02:13,890
So this allows me to narrow down which groups we actually include in our results.

38
00:02:13,890 --> 00:02:16,090
And here is how we do it.

39
00:02:16,110 --> 00:02:21,480
I'm going to put this in a file so we have a record of it right here.

40
00:02:21,840 --> 00:02:27,660
So instead of just selecting title, an average rating from full reviews grouped by title, I'm going

41
00:02:27,660 --> 00:02:32,270
to say grouped by title having and then what is my condition?

42
00:02:32,280 --> 00:02:40,980
I want to make sure that we have a count of rating that is greater than, let's say, more than one

43
00:02:40,980 --> 00:02:43,170
review or more than one rating.

44
00:02:43,650 --> 00:02:45,450
So there's a lot of things I could do here.

45
00:02:45,450 --> 00:02:50,610
I could say only do the groups where title is Archer, but that doesn't really make sense.

46
00:02:51,030 --> 00:02:52,560
Why would I do that?

47
00:02:53,070 --> 00:02:54,150
I can do it.

48
00:02:54,150 --> 00:02:57,240
I only get the group where title is Archer.

49
00:02:57,600 --> 00:02:59,580
Silly but totally valid.

50
00:02:59,580 --> 00:03:03,690
So I'm going to do where the count of rating is greater than one.

51
00:03:03,960 --> 00:03:11,130
So go through full reviews and group every row together by title, but I only care about the ones that

52
00:03:11,130 --> 00:03:13,650
have a count of rating greater than one.

53
00:03:13,890 --> 00:03:18,180
Once you've done that, give me the title and the average rating, we run it.

54
00:03:18,570 --> 00:03:20,480
And did we lose any?

55
00:03:20,490 --> 00:03:22,530
I'm not even sure if we had any.

56
00:03:23,190 --> 00:03:23,910
Let's see.

57
00:03:23,940 --> 00:03:25,920
Let's get rid of the having portion.

58
00:03:26,580 --> 00:03:27,110
Yes.

59
00:03:27,120 --> 00:03:28,570
Halt and catch fire.

60
00:03:28,590 --> 00:03:31,350
I think that the only one we lost anyway.

61
00:03:31,350 --> 00:03:32,550
We lost some, right?

62
00:03:32,580 --> 00:03:33,840
Seinfeld was at the end.

63
00:03:33,840 --> 00:03:35,850
Now we have Seinfeld and Halt and Catch Fire.

64
00:03:35,850 --> 00:03:41,310
And you know how it could make this more obvious is by also including a count of rating.

65
00:03:42,120 --> 00:03:46,170
Let's call this as review count.

66
00:03:47,710 --> 00:03:48,310
Sure.

67
00:03:48,670 --> 00:03:49,230
Okay.

68
00:03:49,630 --> 00:03:57,370
So if I do that now, we can see that all of these have a review count of five, two, four, five,

69
00:03:57,370 --> 00:03:58,360
three, two.

70
00:03:58,900 --> 00:04:05,500
And if I didn't have this having on there, we included Halt and Catch Fire that had one review.

71
00:04:05,800 --> 00:04:13,150
So that's the concept of having it allows us to filter our groups that we are creating with group by

72
00:04:13,180 --> 00:04:16,390
and control the output, control the results that we get back.

73
00:04:16,690 --> 00:04:22,180
So it's different than where where allows us to control what we're selecting to begin with.

74
00:04:22,180 --> 00:04:28,810
But then after we've grouped things, that is where we use having to control what groups we include.