1 00:00:00,150 --> 00:00:00,500 Okay. 2 00:00:00,600 --> 00:00:02,640 Moving on to our next little challenge here. 3 00:00:03,330 --> 00:00:05,790 So here's what the end result should look like. 4 00:00:05,970 --> 00:00:11,580 We have two fields just like we did in the previous one, but there's quite a bit of difference. 5 00:00:11,880 --> 00:00:17,520 If you notice, we have titles still, so that's the same, but we only have one entry per series, 6 00:00:17,520 --> 00:00:18,390 so we don't have. 7 00:00:18,780 --> 00:00:19,350 Archer, Archer. 8 00:00:19,350 --> 00:00:19,860 Archer, Archer. 9 00:00:19,860 --> 00:00:20,430 Archer. 10 00:00:20,760 --> 00:00:23,400 We just have Archer once somewhere. 11 00:00:23,400 --> 00:00:23,900 Where are you? 12 00:00:23,910 --> 00:00:24,510 Over here. 13 00:00:25,290 --> 00:00:28,590 But we also have average rating. 14 00:00:28,710 --> 00:00:34,050 So this is for all the reviews, all the ratings for a given title, put them all together and find 15 00:00:34,050 --> 00:00:34,830 the average. 16 00:00:35,100 --> 00:00:40,820 And then the other thing is that the results are then ordered from lowest average to highest. 17 00:00:40,830 --> 00:00:46,890 So basically we're going to work with very similar data, take it same approach, join the same two 18 00:00:46,890 --> 00:00:47,280 tables. 19 00:00:47,280 --> 00:00:54,750 We're joining series with reviews, but then we're going to group them together and then we're going 20 00:00:54,750 --> 00:00:58,890 to average the rating and then we're going to order by average rating. 21 00:00:58,890 --> 00:01:03,960 And just like the previous problem, there are certain series that don't have reviews. 22 00:01:03,960 --> 00:01:07,320 They are not showing up here, so we don't need to worry about that. 23 00:01:07,800 --> 00:01:13,260 If we're worrying about nulls or non-existent data, that's not a problem for this particular challenge. 24 00:01:14,160 --> 00:01:16,620 So we're going to call this challenge. 25 00:01:19,110 --> 00:01:21,660 Two average rating. 26 00:01:23,370 --> 00:01:26,880 So if we just actually type a very similar thing. 27 00:01:26,880 --> 00:01:32,940 So select title and rating from series. 28 00:01:34,170 --> 00:01:41,520 Join reviews on series dot ID equals reviews dot series ID. 29 00:01:41,700 --> 00:01:43,590 This is going to get us really close. 30 00:01:46,370 --> 00:01:48,940 We have all the data we need right here. 31 00:01:48,950 --> 00:01:49,430 This is it. 32 00:01:49,430 --> 00:01:54,780 We don't need IDs or we don't need to worry about release years or genres or anything. 33 00:01:54,800 --> 00:01:55,730 This is all we need. 34 00:01:56,570 --> 00:02:01,820 However, we need to group things together so that all Archer reviews are together. 35 00:02:02,090 --> 00:02:05,300 All Arrested Development reviews are together and so on. 36 00:02:06,470 --> 00:02:08,960 So then that begs the question, how do we group them? 37 00:02:08,960 --> 00:02:10,039 What's the best way? 38 00:02:10,490 --> 00:02:15,800 And there's a couple of approaches that really we could do it based off of the name the title. 39 00:02:16,640 --> 00:02:22,310 But that assumes that there's only one show named Archer, one show named Bob's Burgers, which is probably 40 00:02:22,310 --> 00:02:24,530 true 99.9% of the time. 41 00:02:24,650 --> 00:02:30,800 But it's possible that we have shows with the same name, or maybe there's a remake of a show. 42 00:02:30,860 --> 00:02:31,830 I'm trying to think of. 43 00:02:31,850 --> 00:02:37,370 I know there's been examples of this, whether it's an older show that's been remade more recently, 44 00:02:37,610 --> 00:02:43,940 but basically it's best to not have to worry about that because we're not making we're not enforcing 45 00:02:43,940 --> 00:02:44,060 that. 46 00:02:44,060 --> 00:02:45,650 The name of the show is unique. 47 00:02:46,100 --> 00:02:51,890 If we go to series, the only thing that has to be unique is ID, so it's much better to group based 48 00:02:51,890 --> 00:02:55,870 off of that ID and of course it's here. 49 00:02:55,880 --> 00:03:02,480 Well, we're not seeing it printed out, but we can easily see it if we just add in series ID. 50 00:03:03,260 --> 00:03:04,670 So now if we run this. 51 00:03:06,790 --> 00:03:09,250 You can see we have that ID there. 52 00:03:09,250 --> 00:03:16,750 So even if we had another show named Archer down at the bottom with ID of 15, if we group by the series 53 00:03:16,750 --> 00:03:21,760 ID, then they'll be unique and they won't muddy the averages. 54 00:03:21,760 --> 00:03:22,950 They won't muddy the group by. 55 00:03:23,530 --> 00:03:24,790 So that should work fine. 56 00:03:25,810 --> 00:03:32,800 And we don't actually need that series ID anymore, but we're going to do a group by and we need to 57 00:03:32,800 --> 00:03:34,870 do series ID that's important. 58 00:03:34,870 --> 00:03:44,410 If we just do ID, that is confusing because it could also be refused at ID just to showcase that if 59 00:03:44,410 --> 00:03:48,010 we just select star and I'll get rid of the group by. 60 00:03:49,500 --> 00:03:53,970 If we just like Star, you can see, even though it's still messy. 61 00:03:53,970 --> 00:03:57,150 We've got one ID here and we've got another ID here. 62 00:03:57,150 --> 00:04:01,500 So this is the series ID and this here is the reviews ID. 63 00:04:02,100 --> 00:04:03,690 So we need to be explicit. 64 00:04:04,470 --> 00:04:06,150 So let's undo our changes. 65 00:04:06,150 --> 00:04:07,350 Go back to what we had. 66 00:04:07,710 --> 00:04:14,790 We're going to group by series dot ID, and if we just do that, it doesn't get us that far. 67 00:04:15,360 --> 00:04:16,470 It gets us closer. 68 00:04:16,470 --> 00:04:21,269 But rating is just going to be basically the first one luck of the draw. 69 00:04:21,269 --> 00:04:23,250 Essentially, it's not an average. 70 00:04:23,280 --> 00:04:25,350 It's just showing us one rating. 71 00:04:25,350 --> 00:04:30,510 Even though they're grouped by this ID here, we're still not getting the average. 72 00:04:30,510 --> 00:04:32,490 And that's a very simple thing to change. 73 00:04:32,490 --> 00:04:37,050 You just need to do average here, which we should be comfortable with by now. 74 00:04:37,620 --> 00:04:43,890 And let's give it an alias average rating just like that. 75 00:04:44,980 --> 00:04:45,550 Okay. 76 00:04:45,940 --> 00:04:46,870 Let's take a look. 77 00:04:47,950 --> 00:04:48,750 Looking good. 78 00:04:48,760 --> 00:04:50,800 We've got title and average rating. 79 00:04:51,370 --> 00:04:52,180 A couple of things. 80 00:04:52,180 --> 00:04:56,410 One, we need to get rid of ID, and two, we need to change the order. 81 00:04:57,820 --> 00:04:59,280 So let's start with a simple one. 82 00:04:59,290 --> 00:05:04,870 Get rid of the ID then let's go ahead and at the end, add an order by. 83 00:05:05,110 --> 00:05:06,420 So how do we want to order them? 84 00:05:06,430 --> 00:05:09,970 Well, what about just using average rating? 85 00:05:10,360 --> 00:05:12,610 So order by average rating. 86 00:05:13,150 --> 00:05:15,550 And then what order do we want? 87 00:05:15,700 --> 00:05:18,520 We can work with the default ascending order. 88 00:05:20,640 --> 00:05:24,780 Because that's what I have here from 5.38 up to 9.9. 89 00:05:25,320 --> 00:05:28,170 And there we go, 5.38, up to 9.9. 90 00:05:28,860 --> 00:05:30,800 Now, that's basically the end. 91 00:05:30,810 --> 00:05:36,330 But I do want to highlight that these reviews are a little bit accurate, in my opinion, at least. 92 00:05:36,960 --> 00:05:39,030 General Hospital, not my favorite show. 93 00:05:39,450 --> 00:05:41,190 It only gets 5.3 on average. 94 00:05:41,190 --> 00:05:42,240 Probably well deserved. 95 00:05:42,240 --> 00:05:45,390 I'm sorry if that's a show close to your heart. 96 00:05:46,230 --> 00:05:52,350 But I want to highlight amazing show that nobody watches called Halt and Catch Fire. 97 00:05:52,380 --> 00:05:54,370 I mean, look at this has a 9.9. 98 00:05:54,390 --> 00:05:55,710 How did that even happen? 99 00:05:56,070 --> 00:05:57,540 I mean, who decided that? 100 00:05:57,750 --> 00:06:00,360 That's real data coming from real people. 101 00:06:00,360 --> 00:06:02,070 Millions of ratings. 102 00:06:02,250 --> 00:06:03,730 And the average is 9.9. 103 00:06:03,750 --> 00:06:04,590 Fantastic. 104 00:06:04,810 --> 00:06:05,070 Okay. 105 00:06:05,070 --> 00:06:06,180 But really, this is a great show. 106 00:06:06,220 --> 00:06:07,220 Nobody watches it. 107 00:06:07,230 --> 00:06:11,040 I highly encourage you to watch it, especially if you have any interest in computers. 108 00:06:11,040 --> 00:06:12,120 The history of computers. 109 00:06:12,120 --> 00:06:13,190 History of the Internet. 110 00:06:13,200 --> 00:06:17,230 Although I will say season one is a little slow, but hang in there if you do want to watch it. 111 00:06:17,250 --> 00:06:18,090 All right. 112 00:06:18,090 --> 00:06:20,940 So we're moving on to our next problem in the next video. 113 00:06:21,480 --> 00:06:22,680 Watch, halt and catch fire.