WEBVTT 00:00.130 --> 00:03.330 OK moving on to our next little challenge here. 00:03.480 --> 00:06.070 So here's what the end result should look like. 00:06.120 --> 00:09.160 We have two fields just like we did in the previous one. 00:09.390 --> 00:13.680 But there is quite a bit of difference if you notice we have titles still. 00:13.680 --> 00:19.570 So that's the same but we only have one entry per series so we don't have you know Archer Archer Archer 00:19.570 --> 00:25.470 Archer Archer we just have Archer wants somewhere where are you over here. 00:25.470 --> 00:28.520 But we also have average rating. 00:28.860 --> 00:34.140 So this is for all of the reviews all the ratings for a given title put them all together and find the 00:34.140 --> 00:40.810 average and then the other thing is that the results are then ordered from lowest average to highest. 00:40.980 --> 00:46.830 So basically we're going to work with very similar data and take that same approach join the same two 00:46.830 --> 00:54.480 tables we're joining series with reviews but then we're going to group them together and then we're 00:54.480 --> 00:59.910 going to average the rating and then we're going to order by average rating and just like the previous 00:59.910 --> 01:03.930 problem there are certain series that don't have reviews. 01:03.940 --> 01:07.300 They're not showing up here so we don't need to worry about that. 01:07.420 --> 01:13.670 Well if we're worrying about Knoll's or non-existent data that's not a problem for this particular challenge. 01:14.310 --> 01:16.380 So we're going to call this challenge 01:19.140 --> 01:23.390 to average rating. 01:23.480 --> 01:26.820 So if we just actually type a very similar thing. 01:26.900 --> 01:37.740 So select title and rating from series join previews on series. 01:37.760 --> 01:40.190 ID equals reviews. 01:40.490 --> 01:41.740 Series ID. 01:41.870 --> 01:46.470 This is going to get us really close. 01:46.480 --> 01:48.860 We have all the data we need right here. 01:49.010 --> 01:49.390 This is it. 01:49.390 --> 01:54.790 We don't need IDs or we don't need to worry about release scares or genres or anything. 01:54.790 --> 01:56.120 This is all we need. 01:56.560 --> 02:02.200 However we need to group things together so that all Archer reviews are together. 02:02.200 --> 02:05.310 All Arrested Development reviews are together and so on. 02:06.560 --> 02:10.180 So then that begs the question how do we group them what's the best way. 02:10.610 --> 02:17.060 And there's a couple of approaches that really we could do it based off of the name the title but that 02:17.060 --> 02:22.610 assumes that there's only one show named Archer one show named Bob's Burgers which is probably true 02:22.730 --> 02:28.940 ninety nine point nine percent of the time but it's possible that we have shows with the same name. 02:28.940 --> 02:31.900 Or maybe there's a remake of a show I'm trying to think of. 02:31.970 --> 02:38.120 I know there's been examples of this where there is an older show that's been remade more recently but 02:38.210 --> 02:44.030 basically it's best to not have to worry about it because we're not making we're not enforcing that 02:44.030 --> 02:45.680 the name of the show is unique. 02:46.130 --> 02:49.560 If we go to series The only thing that has to be unique is ID. 02:49.790 --> 02:53.030 So it's much better to group based off of that ID. 02:53.570 --> 02:55.920 And of course it's here. 02:56.070 --> 03:02.760 Well we're not seeing it printed out but we can easily see it if we just add in series ID. 03:03.320 --> 03:09.210 So now if we run this you can see we have that ID there. 03:09.350 --> 03:16.700 So even if we had another show named Archer down at the bottom with idea 15 if we grouped by the series 03:16.700 --> 03:23.460 ID then they'll be unique and they won't you know Muddy the averages and muddy the group. 03:23.630 --> 03:25.850 So that should work fine. 03:25.870 --> 03:32.930 We don't actually need that series idea anymore but we're going to do a group buy and we need to do 03:32.930 --> 03:34.830 series idea that's important. 03:35.030 --> 03:44.390 If we just do id that is confusing because it could also be reviews that Id just to showcase that if 03:44.390 --> 03:51.280 we just select star I'll get rid of the group by just like star. 03:51.680 --> 03:57.200 You can see even though it's still messy We've got one idea here and we've got another idea here. 03:57.260 --> 04:01.940 So this is the series ID and this here is the reviews ID. 04:02.240 --> 04:03.760 So we need to be explicit. 04:04.190 --> 04:06.110 OK so let's undo our changes. 04:06.230 --> 04:07.580 Go back to what we had. 04:07.820 --> 04:16.910 We're going to group by series ID and if we just do that doesn't get us that far gets us closer but 04:16.970 --> 04:20.660 raiding is just going to be the face for the first one. 04:20.660 --> 04:21.900 Luck of the draw essentially. 04:21.980 --> 04:28.550 It's not an average that's just showing us one rating even though they're grouped by this idea here. 04:28.850 --> 04:32.540 We're still not getting the average and that's a very simple thing to change. 04:32.540 --> 04:37.370 We just need to do average here which we should be comfortable with by now. 04:37.690 --> 04:44.320 And let's give it an alias average rating just like that. 04:44.920 --> 04:45.530 OK. 04:45.950 --> 04:47.960 Let's take a look. 04:47.960 --> 04:48.840 Looking good. 04:48.860 --> 04:51.440 We've got title an average rating. 04:51.440 --> 04:52.200 Couple of things. 04:52.250 --> 04:54.730 One we need to get rid of ID and. 04:54.800 --> 04:56.840 We need to change the order. 04:57.920 --> 04:59.330 So let's start with a simple one. 04:59.330 --> 05:03.180 Get rid of the ID then let's go ahead. 05:03.180 --> 05:04.920 And at the end add an ORDER BY. 05:05.210 --> 05:06.400 So how do we want to order them. 05:06.500 --> 05:10.120 Well what about just using average rating. 05:10.470 --> 05:12.790 So ordered by average rating. 05:13.310 --> 05:15.630 And then what order do we want. 05:15.890 --> 05:23.980 We can work with the default ascending order because that's what I have here from 5.3 eight up to nine 05:23.980 --> 05:25.100 point nine. 05:25.450 --> 05:26.100 And there we go. 05:26.140 --> 05:28.840 Five point 3:08 up to nine point nine. 05:28.990 --> 05:30.790 Now that's basically the end. 05:30.850 --> 05:36.850 But I do want to highlight that these reviews are a little bit accurate in my opinion at least. 05:37.030 --> 05:42.230 General Hospital not my favorite show only gets five point three on average probably well deserved. 05:42.230 --> 05:48.420 I'm sorry if that's shoke close to your heart but I want to highlight. 05:49.000 --> 05:52.350 Amazing show that nobody watches called Halt And Catch Fire. 05:52.510 --> 05:54.480 I mean look at this has a 9.9. 05:54.490 --> 05:55.810 How did that even happen. 05:56.170 --> 06:02.810 I mean who decided that that's real data coming from real people millions of ratings and the average 06:02.810 --> 06:03.700 is nine point nine. 06:03.700 --> 06:04.580 Fantastic. 06:04.830 --> 06:05.040 OK. 06:05.050 --> 06:06.160 But really this is a great show. 06:06.160 --> 06:07.310 Nobody watches it. 06:07.330 --> 06:12.090 I highly encourage you to watch it especially if you have any interest in computers the history of computers 06:12.100 --> 06:13.180 history of the Internet. 06:13.300 --> 06:17.280 Although I will say Season One is a little slow but hang in there if you do want to watch it. 06:17.290 --> 06:18.130 All right. 06:18.280 --> 06:21.540 So we're moving on to our next problem in the next video. 06:21.580 --> 06:22.700 Watch out and catch fire.