1
00:00:00,090 --> 00:00:04,590
Next up, we've got a little bit more of a complicated result we're looking for.

2
00:00:05,070 --> 00:00:11,280
It involves each reviewer so their first name and their last name and then the number of reviews that

3
00:00:11,280 --> 00:00:12,210
they've left.

4
00:00:12,570 --> 00:00:17,700
And then the minimum score, the maximum score, the average score.

5
00:00:17,700 --> 00:00:25,380
And then status here should be a conditional that will be either active or inactive depending on whether

6
00:00:25,530 --> 00:00:27,720
a user has left a review or not.

7
00:00:27,720 --> 00:00:34,980
So Marlon Crawford, Crawford, whoever that is, has zero ratings that they've zero reviews, so they're

8
00:00:34,980 --> 00:00:37,350
considered inactive, but everyone else is active.

9
00:00:37,530 --> 00:00:39,240
So there's a lot going on here.

10
00:00:39,240 --> 00:00:44,280
But at the end of the day, it's just some aggregate functions we covered a while ago, some grouping

11
00:00:44,280 --> 00:00:45,420
and a join.

12
00:00:45,450 --> 00:00:46,860
What are we joining?

13
00:00:46,860 --> 00:00:50,040
Well, information about reviews and reviewers.

14
00:00:50,280 --> 00:00:51,720
So go ahead and try it.

15
00:00:51,990 --> 00:00:53,850
I will start right now.

16
00:00:53,850 --> 00:00:58,920
Here's my attempt so we know that we want first name and last name, but I'm just going to start by

17
00:00:58,920 --> 00:01:03,240
doing a join between reviewers and reviews.

18
00:01:03,360 --> 00:01:06,900
So select star from reviewers.

19
00:01:07,790 --> 00:01:21,530
Join reviews and then we'll do on reviewer dot ID is equal to reviews dot reviewer ID.

20
00:01:21,680 --> 00:01:22,130
All right.

21
00:01:22,130 --> 00:01:23,750
So we'll start with an inner join.

22
00:01:24,500 --> 00:01:25,100
And there we go.

23
00:01:25,100 --> 00:01:28,550
We have the overlap between users and reviews.

24
00:01:29,810 --> 00:01:36,200
But remember, we also want to include Marlon Crawford and any other users that don't have reviews.

25
00:01:36,200 --> 00:01:40,250
And at the moment, he's not in here because we did an inner join.

26
00:01:40,250 --> 00:01:46,310
If I did a left join, we'll get every row from reviewers plus any matching information from reviews.

27
00:01:46,310 --> 00:01:48,890
So I'm going to do a left join instead.

28
00:01:51,950 --> 00:01:52,450
Okay.

29
00:01:52,460 --> 00:01:54,290
And now we have Marlon Crawford.

30
00:01:54,650 --> 00:02:02,780
Now we want to do a group by and we were going to group by first and last name combined although you

31
00:02:02,780 --> 00:02:10,699
could in this example data set just do last name but we're going to do a group by first name, comma,

32
00:02:10,699 --> 00:02:11,600
last name.

33
00:02:11,960 --> 00:02:19,370
And then instead of selecting star, let's select first name, last name, and then let's do count.

34
00:02:20,210 --> 00:02:24,410
But I'm going to do count of rating instead of Count Star.

35
00:02:24,440 --> 00:02:25,970
We'll talk about that in a moment.

36
00:02:27,860 --> 00:02:29,000
And what do we see?

37
00:02:29,030 --> 00:02:31,360
Thomas Stoneman five Reviews.

38
00:02:31,370 --> 00:02:33,290
Domingo Cortez ten reviews.

39
00:02:33,290 --> 00:02:34,690
Cold Steel ten reviews.

40
00:02:34,700 --> 00:02:37,180
Marlon Crawford zero reviews.

41
00:02:37,190 --> 00:02:39,260
That's the count, which is correct.

42
00:02:39,920 --> 00:02:44,720
Now, if I instead had done Count of Star, that would not quite be right.

43
00:02:44,750 --> 00:02:46,370
It would be very close.

44
00:02:46,370 --> 00:02:48,260
But this is counting the rows.

45
00:02:48,260 --> 00:02:55,790
And as we know, Marlon Crawford does not have any reviews, but he does have one row.

46
00:02:55,820 --> 00:02:57,860
So the count here would be one.

47
00:02:57,860 --> 00:03:04,550
But I want the count of the actual ratings, not the count of how many times he appears in our joint

48
00:03:04,550 --> 00:03:06,170
table, because here is the joint table.

49
00:03:06,200 --> 00:03:11,930
He has no ratings, no anything related to ratings, but his name is there and there's a row.

50
00:03:11,960 --> 00:03:13,010
He occurs once.

51
00:03:13,010 --> 00:03:15,590
So when I do Count Star, it's included.

52
00:03:15,590 --> 00:03:17,630
But if I do count of rating.

53
00:03:19,270 --> 00:03:20,470
It's not included.

54
00:03:21,020 --> 00:03:22,630
Okay, So that gives us the count.

55
00:03:24,040 --> 00:03:25,270
What do we have next?

56
00:03:25,300 --> 00:03:28,240
We need to get the minimum and maximum.

57
00:03:28,740 --> 00:03:31,060
Okay, so let's do this on separate lines.

58
00:03:33,070 --> 00:03:33,730
First name.

59
00:03:33,730 --> 00:03:35,020
Last name, count.

60
00:03:37,130 --> 00:03:42,830
We'll do the min rating followed by the MAX rating.

61
00:03:44,350 --> 00:03:45,550
Run this query.

62
00:03:47,440 --> 00:03:49,900
Okay, so it looks good.

63
00:03:49,900 --> 00:03:53,770
We see Thomas Donovan's minimum is seven, his max is 9.5.

64
00:03:53,770 --> 00:03:55,480
That is what I had on the slide.

65
00:03:55,660 --> 00:04:00,790
However, for Marlon Crawford, it's not quite correct because we see null.

66
00:04:00,790 --> 00:04:04,300
And in the example I showed you, I mean, no, it could be fine.

67
00:04:04,300 --> 00:04:09,220
But in the example I showed, I want zero for count min, max and average.

68
00:04:09,220 --> 00:04:12,730
So we can use if no, if you remember that.

69
00:04:13,270 --> 00:04:16,810
So if null replace it with zero.

70
00:04:17,350 --> 00:04:21,610
If null replace max of rating with zero.

71
00:04:21,700 --> 00:04:22,810
Try it again.

72
00:04:24,580 --> 00:04:25,300
And there we are.

73
00:04:25,300 --> 00:04:27,640
We see zero and zero for min and max.

74
00:04:27,970 --> 00:04:29,950
Let's quickly give these new names.

75
00:04:30,100 --> 00:04:33,220
So we'll call this as count.

76
00:04:34,640 --> 00:04:36,860
This one is as Min.

77
00:04:38,110 --> 00:04:39,700
As Max.

78
00:04:42,420 --> 00:04:43,080
Cool.

79
00:04:43,380 --> 00:04:44,430
That's looking better.

80
00:04:44,760 --> 00:04:48,870
The next thing we have to do is get the average so that when simple enough as well.

81
00:04:48,990 --> 00:04:54,150
Same deal, average rating, we'll call it as average.

82
00:04:54,630 --> 00:04:57,210
But once again, we'll run into trouble.

83
00:04:57,990 --> 00:05:04,290
When it comes to Marlin Crawford, we get no, because the average of nothing is nothing.

84
00:05:04,290 --> 00:05:07,650
But I'm going to replace that with zero just to match the slide.

85
00:05:09,770 --> 00:05:10,100
If.

86
00:05:10,100 --> 00:05:10,790
No.

87
00:05:14,190 --> 00:05:14,850
Zero.

88
00:05:17,090 --> 00:05:18,560
Okay, we're almost there.

89
00:05:19,880 --> 00:05:21,140
The final piece here.

90
00:05:21,260 --> 00:05:22,910
I guess we should round this too.

91
00:05:22,940 --> 00:05:24,110
Did I round it in the slide?

92
00:05:24,110 --> 00:05:25,280
Nope, I didn't.

93
00:05:25,280 --> 00:05:33,650
But once again, we'll round it so we can round it first round the result of if no or we could round

94
00:05:33,650 --> 00:05:34,850
average and then do.

95
00:05:34,850 --> 00:05:37,790
If no either one's fine but I'll just do the round on the outside.

96
00:05:38,600 --> 00:05:41,690
Round if no to two places.

97
00:05:43,150 --> 00:05:44,110
Okay.

98
00:05:46,180 --> 00:05:50,380
And there are 0.00 and 8.0 to just two digits.

99
00:05:50,920 --> 00:05:53,620
And now we have the final piece, our status.

100
00:05:53,620 --> 00:05:56,190
So status needs to be active or inactive.

101
00:05:56,200 --> 00:06:00,320
If count is greater than zero, we'll make it active.

102
00:06:00,340 --> 00:06:01,540
So we'll do this.

103
00:06:01,690 --> 00:06:02,910
There's two options.

104
00:06:02,920 --> 00:06:05,840
One is to use case, which is what we've seen before.

105
00:06:05,860 --> 00:06:14,110
So I'll show that first case and end and then we'll say when count of rating, let me capitalize it

106
00:06:14,110 --> 00:06:15,130
to be consistent.

107
00:06:15,940 --> 00:06:24,910
When the count of rating is greater than zero, we'll call you, we'll say then active and then we can

108
00:06:24,910 --> 00:06:28,360
say else inactive OC.

109
00:06:28,390 --> 00:06:29,850
So that should work for us.

110
00:06:29,860 --> 00:06:31,990
We'll call this as status.

111
00:06:32,990 --> 00:06:39,120
Try running this query and we see active for all the users except Marlon Crawford is inactive.

112
00:06:39,140 --> 00:06:40,130
That's correct.

113
00:06:40,130 --> 00:06:41,380
And we're technically done.

114
00:06:41,390 --> 00:06:42,560
That's everything we needed.

115
00:06:43,220 --> 00:06:45,080
But as I said, there's another option.

116
00:06:45,110 --> 00:06:51,860
Case is great when we have two, three, four, five, multiple cases, multiple when then pairs.

117
00:06:51,860 --> 00:06:56,000
But if it's just an if else, which is what we have right now, do this thing.

118
00:06:56,000 --> 00:07:00,020
And if that's not true, then otherwise this thing there's a shorter option.

119
00:07:00,260 --> 00:07:04,580
I'll write it on a second line or a second query.

120
00:07:05,270 --> 00:07:13,940
Instead of having to do this case thing, we can simply say if and then if count of rating is greater

121
00:07:13,940 --> 00:07:18,710
than zero, then after comma will provide that means active.

122
00:07:19,250 --> 00:07:22,040
Otherwise it means inactive.

123
00:07:22,610 --> 00:07:27,170
So we have our condition and then the true portion and the false portion.

124
00:07:27,170 --> 00:07:29,030
And we'll call this as status.

125
00:07:29,780 --> 00:07:34,220
And if I run this one, same exact result.

126
00:07:34,820 --> 00:07:38,030
The difference, of course, is that this allows two choices in.

127
00:07:38,060 --> 00:07:38,590
That's it.

128
00:07:38,600 --> 00:07:40,580
If this is true, we get active.

129
00:07:40,580 --> 00:07:42,200
If it's false, we get inactive.

130
00:07:42,410 --> 00:07:47,960
But with this, with the case statement, I could have multiple cases so we could change it up a tiny

131
00:07:47,960 --> 00:07:50,330
bit if we wanted to work and figure out.

132
00:07:50,330 --> 00:07:56,630
Power users will say if the count of your ratings is greater than or equal to ten, then we'll call

133
00:07:56,630 --> 00:08:04,340
you Oops ten, we'll call you a power user and then we'll have another when this time will say when

134
00:08:04,340 --> 00:08:09,620
count of rating is greater than or equal to zero, then you are.

135
00:08:09,620 --> 00:08:15,800
I didn't spell power user rate active and then else you're inactive because that means you're less than

136
00:08:15,800 --> 00:08:16,490
zero.

137
00:08:17,390 --> 00:08:20,900
So just an example of why a case is more flexible.

138
00:08:20,900 --> 00:08:22,190
It's more work.

139
00:08:22,190 --> 00:08:28,760
We do see now Power user we've got two of them because Domingo and Colt have ten reviews active.

140
00:08:28,760 --> 00:08:33,320
It's anything between zero or one and ten and then zero is inactive.

141
00:08:33,320 --> 00:08:37,159
So that's actually incorrect there because I have greater than or equal to zero.

142
00:08:37,159 --> 00:08:42,350
I want greater than zero, meaning if you're equal to zero, you're in the low portion.

143
00:08:43,039 --> 00:08:44,090
Try it one more time.

144
00:08:44,630 --> 00:08:45,470
And there we go.

145
00:08:45,590 --> 00:08:47,330
So this is above and beyond what we needed.

146
00:08:47,330 --> 00:08:52,970
But I just wanted to illustrate what we could do with the case statement that we can't do with if but

147
00:08:52,970 --> 00:08:58,730
if makes it really short and nice and succinct, if we just have an if else, try this.

148
00:08:58,730 --> 00:09:00,080
Otherwise do this.

149
00:09:00,080 --> 00:09:04,010
If that's how you can express your logic, then if it's very simple.

150
00:09:04,370 --> 00:09:07,580
Otherwise, a case allows us to have multiple cases.

151
00:09:08,150 --> 00:09:10,160
Okay, we've got one more coming up.