1 00:00:00,060 --> 00:00:01,320 Here's our next challenge. 2 00:00:01,320 --> 00:00:07,140 What we're trying to find out is the average rating for each of our TV series that we have. 3 00:00:07,140 --> 00:00:11,100 And then I want it to be sorted or ordered from lowest to highest. 4 00:00:11,100 --> 00:00:13,490 So this will involve a couple of things, right? 5 00:00:13,500 --> 00:00:15,690 We have to have a group buy at some point. 6 00:00:15,690 --> 00:00:17,010 We need to have a join. 7 00:00:17,010 --> 00:00:18,360 We need to have an order buy. 8 00:00:18,360 --> 00:00:19,860 We need to do an average. 9 00:00:20,460 --> 00:00:22,190 And I think that's it. 10 00:00:22,200 --> 00:00:28,920 So we have the title of every show, along with all of the reviews left for that show, grouped together, 11 00:00:28,920 --> 00:00:30,870 averaged and sorted. 12 00:00:31,080 --> 00:00:34,560 Try it if you'd like, and I'll go ahead and do it myself. 13 00:00:34,740 --> 00:00:41,520 So the first thing we want would be basically to start with what we did in the last video where we select 14 00:00:41,520 --> 00:00:42,870 the title and the rating. 15 00:00:42,870 --> 00:00:43,830 Is that all we need? 16 00:00:43,860 --> 00:00:50,490 Title and rating from all of the shows where they match up, where they join together, and then I want 17 00:00:50,490 --> 00:00:57,840 to group them together by title so that I can average the ratings because now I have all of the reviews 18 00:00:57,840 --> 00:01:02,280 left for Archer, all of the ratings for Arrested Development, all of the ones for Freaks and Geeks. 19 00:01:02,280 --> 00:01:04,319 Then I just grouped them together. 20 00:01:04,319 --> 00:01:05,550 So I'll duplicate this. 21 00:01:05,550 --> 00:01:12,120 I'll do it down here group by title, and that will get me part of the way there. 22 00:01:12,120 --> 00:01:16,500 But once I've done that grouping by title, I don't want just rating. 23 00:01:16,500 --> 00:01:22,290 There isn't one rating anymore When we grouped by title for Fargo, well, there's two ratings. 24 00:01:22,290 --> 00:01:26,280 Freaks and Geeks has four ratings, so I want to do the average. 25 00:01:26,280 --> 00:01:29,190 So average call the average function. 26 00:01:29,950 --> 00:01:31,930 Let's see if that works for us. 27 00:01:33,430 --> 00:01:36,450 We're now getting an average we could round this. 28 00:01:36,460 --> 00:01:39,520 We haven't really talked about round, but there is a function called round. 29 00:01:39,550 --> 00:01:42,730 These are a little long, but they're accurate. 30 00:01:42,760 --> 00:01:47,680 The next thing we want to do is sort them from lowest to highest. 31 00:01:47,680 --> 00:01:49,200 So we'll do an order by. 32 00:01:49,210 --> 00:01:54,430 But in order to do an order by, I'm going to give this a name, as we'll call it, average rating. 33 00:01:55,310 --> 00:01:59,450 And I'll do this on separate lines just like that. 34 00:02:00,680 --> 00:02:08,030 Okay, so select title and average rating as average rating grouped by title, and then at the end order 35 00:02:08,030 --> 00:02:10,490 by average rating. 36 00:02:10,490 --> 00:02:13,130 And we want ascending lowest to highest. 37 00:02:13,250 --> 00:02:20,060 So we get General Hospital with its mighty 5.38 all the way up to halt and catch fire with its 9.9 grade 38 00:02:20,060 --> 00:02:20,510 show. 39 00:02:20,540 --> 00:02:25,700 Got to watch that And that is what we were supposed to do, right? 40 00:02:25,700 --> 00:02:27,500 5.38 up to nine nine. 41 00:02:27,500 --> 00:02:28,640 We called it average rating. 42 00:02:28,640 --> 00:02:29,600 We sorted. 43 00:02:29,630 --> 00:02:30,560 That's all good. 44 00:02:30,800 --> 00:02:36,410 Now, I did say we could round this, I didn't in this screenshot, but if we wanted to it looks like 45 00:02:36,410 --> 00:02:38,390 this we round. 46 00:02:39,260 --> 00:02:40,460 That's the name of the function. 47 00:02:40,460 --> 00:02:43,700 Actually, I should just show it to you Here, select round. 48 00:02:44,330 --> 00:02:46,250 How about 3.14159? 49 00:02:46,940 --> 00:02:51,650 If I just try and round it this way, you'll see that it rounds it to the nearest whole number. 50 00:02:51,650 --> 00:02:55,100 So if I had done 3.99, it rounds it up to four. 51 00:02:55,520 --> 00:03:02,300 But if I instead say a second argument, a second value after the thing I'm trying to round, if I say 52 00:03:02,300 --> 00:03:07,370 round two, it rounds it to two digits, 3.14. 53 00:03:07,850 --> 00:03:10,700 And I think two digits is a good number for these ratings. 54 00:03:10,700 --> 00:03:15,170 So we'll do round the average rating to two digits. 55 00:03:15,500 --> 00:03:21,260 This is above and beyond not required, but that's a lot easier to look at and it's just cleaner. 56 00:03:21,840 --> 00:03:22,290 Okay. 57 00:03:22,490 --> 00:03:24,260 Next up, another challenge.