WEBVTT 00:00.080 --> 00:00.510 OK. 00:00.630 --> 00:04.520 So next up we have another nice clean table just three results. 00:04.620 --> 00:09.590 And as you can see what we're doing here is grabbing things based off of genre. 00:09.900 --> 00:10.750 So we only have three. 00:10.750 --> 00:13.320 Fortunately animation comedy and drama. 00:13.560 --> 00:18.880 And then what we're doing is computing the average review the average rating for each of those genres. 00:19.030 --> 00:23.580 So this will be a little similar to what we've already done earlier and this problem said the main difference 00:23.610 --> 00:28.600 is that we're working with genre now rather than just the title of a given series. 00:29.030 --> 00:29.790 OK. 00:30.270 --> 00:34.160 One thing I def need to correct the record here called this challenge five. 00:34.310 --> 00:35.370 I was wrong. 00:35.730 --> 00:40.870 After four after three comes four now we're on Challenge five. 00:42.000 --> 00:42.740 OK. 00:43.100 --> 00:47.670 And we'll just call this genre average ratings. 00:47.840 --> 00:48.280 OK. 00:48.390 --> 00:54.480 So to get this information from our three tables the two that were that we need to identify that we're 00:54.480 --> 01:00.520 working with are the series table because that's where our genre information is stored. 01:00.610 --> 01:03.750 And then the reviews table that where ratings information is stored. 01:03.850 --> 01:06.790 So we don't care about the reviewers the people who review them. 01:06.790 --> 01:07.690 That doesn't matter. 01:07.690 --> 01:10.020 All we need is genres. 01:10.510 --> 01:14.070 And then the ratings so we can start with either one. 01:14.350 --> 01:21.370 Let's start with Star from series just like that. 01:22.150 --> 01:22.720 OK. 01:23.460 --> 01:30.630 So we're going to want to work with is the genre portion but we're going to need to join with the reviews 01:30.630 --> 01:32.450 table so we'll just do that now. 01:32.490 --> 01:33.810 So let's start from series. 01:33.870 --> 01:35.830 Join revues. 01:35.880 --> 01:38.170 Hopefully this seems familiar now. 01:38.430 --> 01:49.020 On series ID equals revues dot series ID and if we leave a star in there we're going to get a lot of 01:49.020 --> 01:52.950 stuff so we don't need all that. 01:53.590 --> 02:00.310 Let's just pick out a few things like title Johnna and rating. 02:01.870 --> 02:02.380 OK. 02:02.590 --> 02:08.460 So now we can see we've got you know all the ratings for Archer all animation of course because they're 02:08.470 --> 02:15.730 the same exact row here with a relevant rating appended So join them together. 02:15.730 --> 02:19.760 It's an inner join and that's what we want. 02:19.780 --> 02:23.640 We don't want to be working with you know non-existent ratings. 02:23.860 --> 02:26.870 We don't want to take let's say What was it. 02:27.190 --> 02:32.180 Pushing Daisies which didn't have any reviews Well that's a comedy I believe. 02:32.320 --> 02:33.130 Maybe it's a draw. 02:33.130 --> 02:33.960 I don't remember. 02:34.090 --> 02:40.090 But whatever it is we don't want to try an average No into the average rating for dramas so the average 02:40.090 --> 02:41.040 rating for comedy. 02:41.080 --> 02:44.210 So we don't want to do a left join you on. 02:44.710 --> 02:45.170 OK. 02:45.340 --> 02:47.620 So we've got this on Sirius ID. 02:47.650 --> 02:49.330 Blah blah blah blah blah. 02:49.330 --> 02:53.460 Now all we need to do is group things by genre. 02:54.010 --> 02:57.170 That's what we're trying to do here rather than by title group by genre 03:01.320 --> 03:05.540 and if we do that we should see this is immediately reduced down to three rows. 03:05.550 --> 03:11.310 Although remember that it's just kind of hiding all the data that lies beneath. 03:11.790 --> 03:16.450 So we have animation here but that doesn't mean that there's only one. 03:16.490 --> 03:21.510 It's just that gripped by when you when you use a group by it's displayed in a very weird awkward way 03:21.510 --> 03:27.480 that I think is kind of confusing but what we want to do is rather than we can get rid of the title 03:27.480 --> 03:31.130 to it rather than just genre rating. 03:31.380 --> 03:39.430 We just want average rating and that should do it for us and we get the average ratings. 03:39.470 --> 03:47.540 There's an alias so I can add that in like that and that's it. 03:47.540 --> 03:48.970 So now we get animation. 03:49.170 --> 03:54.860 It's the lowest rated still quite high comedy is the highest rated 8.1. 03:55.080 --> 03:56.980 And drama is right in the middle. 03:57.030 --> 03:58.240 8.0. 03:58.680 --> 04:03.810 And I suppose this is as good a time as any to introduce the round function. 04:03.840 --> 04:08.340 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 04:08.460 --> 04:12.600 let's just round it to two decimal places. 04:12.600 --> 04:17.470 So it looks like this round and I'll do it on separate lines here. 04:17.790 --> 04:24.930 So we have round average rating but we don't just say round average rating we have to supply what we 04:24.930 --> 04:31.380 want around to two digits for us and then we'll use ads for trading. 04:31.500 --> 04:37.480 So I could have done it all in one line in gigantic gross line in my opinion. 04:37.530 --> 04:43.330 So it's a little better to break it up like this takes up more space but it's a price we pay. 04:43.630 --> 04:43.880 OK. 04:43.890 --> 04:48.690 So around the average two digits and call it average rating. 04:48.900 --> 04:50.830 Now let's double check that works. 04:50.960 --> 04:52.940 And there we go much easier. 04:52.950 --> 04:55.770 You didn't have to do that of course but it figured it was a good thing to show.