1 00:00:00,300 --> 00:00:00,780 All right. 2 00:00:00,780 --> 00:00:06,090 Returning to Min and Max, these built in functions, we saw that we can use them on their own without 3 00:00:06,090 --> 00:00:09,000 any grouping, at least any of our own grouping. 4 00:00:09,000 --> 00:00:12,090 But we can also use them in conjunction with Group VI. 5 00:00:12,090 --> 00:00:17,760 In the same way we can use count on its own or with group by so we can do things like this. 6 00:00:17,760 --> 00:00:21,360 Find the year each author published their first book. 7 00:00:21,360 --> 00:00:27,660 In other words, group all of the books by the same authors together and then find the minimum year 8 00:00:27,660 --> 00:00:28,980 for that author. 9 00:00:29,070 --> 00:00:32,250 So we're not finding the minimum year of the entire table. 10 00:00:32,250 --> 00:00:36,360 We know how to do that, but find the minimum year for each author. 11 00:00:36,810 --> 00:00:38,190 And that would look like this. 12 00:00:38,460 --> 00:00:45,930 Select whatever we want, the first name, the last name, and then the minimum released year from books. 13 00:00:45,930 --> 00:00:47,640 And then we can group by. 14 00:00:47,640 --> 00:00:52,500 In this case, I'm grouping by first name and last name because we do have some duplicated as we've 15 00:00:52,500 --> 00:00:52,890 already seen. 16 00:00:52,890 --> 00:00:57,840 There's two heresies, but we could also just keep it simple to start and just grouped by the author's 17 00:00:57,840 --> 00:00:58,740 last name. 18 00:00:58,890 --> 00:01:00,300 So let's take a look at that. 19 00:01:00,300 --> 00:01:03,360 Let's do I'll write it in a script here. 20 00:01:03,480 --> 00:01:10,110 Select let's do author first, let's do last name from books. 21 00:01:10,110 --> 00:01:14,550 We'll group by author last name. 22 00:01:14,550 --> 00:01:16,320 So that will make us our groups. 23 00:01:16,320 --> 00:01:21,570 And what I want to find is not the count that's going to tell me how many books that author has written. 24 00:01:21,720 --> 00:01:28,230 I want to know what is the minimum released year within each one of those groups? 25 00:01:28,650 --> 00:01:29,760 Let's check it out. 26 00:01:30,120 --> 00:01:37,170 Well, it looks like Lahiri, her youngest book or her first book in the data set at least, is 96. 27 00:01:37,830 --> 00:01:39,300 Steinbeck only has one. 28 00:01:39,300 --> 00:01:40,740 Eggers I know has three books. 29 00:01:40,740 --> 00:01:42,420 The first one is 2001. 30 00:01:43,560 --> 00:01:44,760 Who else is in here? 31 00:01:44,760 --> 00:01:46,890 Saunders 2017. 32 00:01:48,510 --> 00:01:51,630 Gaiman has three books, I think two or three in the data set. 33 00:01:51,630 --> 00:01:56,730 2001 is his earliest book, so we could tweak it and do maximum year. 34 00:01:56,790 --> 00:02:00,480 That would be the latest book that they wrote. 35 00:02:02,270 --> 00:02:03,250 And there we are. 36 00:02:03,260 --> 00:02:07,060 We see here a 2003 game in 2016. 37 00:02:07,070 --> 00:02:08,630 Eggers 2013. 38 00:02:08,930 --> 00:02:10,340 We could even do both. 39 00:02:11,009 --> 00:02:11,150 Right. 40 00:02:11,150 --> 00:02:15,430 I could do Min and Max released least. 41 00:02:16,580 --> 00:02:17,600 Let's try that. 42 00:02:18,840 --> 00:02:21,330 And now we see that Lahiri has two books. 43 00:02:21,330 --> 00:02:22,410 One is from. 44 00:02:22,650 --> 00:02:23,580 Well, this isn't. 45 00:02:23,580 --> 00:02:25,690 Tell us that there's two, but there's at least two. 46 00:02:25,750 --> 00:02:28,260 The minimum released here is 96. 47 00:02:28,260 --> 00:02:29,760 Maximum is 2003. 48 00:02:29,880 --> 00:02:32,140 Eggers 2013 is the maximum. 49 00:02:32,160 --> 00:02:33,630 2001 is the minimum. 50 00:02:34,080 --> 00:02:36,360 And that's the basic concept here. 51 00:02:36,360 --> 00:02:42,890 We formed our groups and then we can, within each group, find the minimum or the max or the count. 52 00:02:42,900 --> 00:02:44,430 I could throw that in here too. 53 00:02:44,460 --> 00:02:45,990 There's no reason I can't. 54 00:02:46,410 --> 00:02:52,260 Let me just split this up a bit, maybe on a second line for each author that we've grouped by their 55 00:02:52,260 --> 00:02:58,020 last name, we can find the last name, their maximum release here, the minimum release year. 56 00:02:58,020 --> 00:03:03,180 How about we find the number of books written So that would be Count Star. 57 00:03:03,180 --> 00:03:08,700 And why don't we alias each one of these so as books? 58 00:03:10,560 --> 00:03:11,310 Written. 59 00:03:12,750 --> 00:03:13,800 And then. 60 00:03:15,010 --> 00:03:19,720 How about we call this one as earliest release? 61 00:03:19,720 --> 00:03:23,950 And this is getting quite long, so I'm going to do this on different lines. 62 00:03:25,300 --> 00:03:28,600 We can format this later, but we'll start like this. 63 00:03:28,720 --> 00:03:29,860 I'll indent it. 64 00:03:30,820 --> 00:03:32,440 So we have our last name. 65 00:03:32,530 --> 00:03:33,700 Put that one down. 66 00:03:34,330 --> 00:03:36,730 The count for each of these groups. 67 00:03:36,910 --> 00:03:37,390 We'll call. 68 00:03:37,420 --> 00:03:41,030 We'll call it books written the maximum released year as there. 69 00:03:41,050 --> 00:03:44,050 That should actually be latest release. 70 00:03:44,500 --> 00:03:48,790 And then minimum release year will be earliest release. 71 00:03:49,240 --> 00:03:54,280 Let's check it out, paste it over here, or just run it from within one of the GIs. 72 00:03:54,280 --> 00:03:56,820 And we see Lahiri has two books. 73 00:03:56,830 --> 00:03:59,920 Latest release 2003 Earliest release 96. 74 00:03:59,950 --> 00:04:01,450 Eggers has three books. 75 00:04:01,450 --> 00:04:04,690 2013 is the latest, 2001 is the earliest. 76 00:04:05,170 --> 00:04:07,510 And let's add one more piece in. 77 00:04:07,630 --> 00:04:10,510 Let's find the longest page count. 78 00:04:10,810 --> 00:04:11,140 Right. 79 00:04:11,140 --> 00:04:17,829 So we could do maximum of pages as longest page count or something like that. 80 00:04:18,700 --> 00:04:21,760 And again, that is for each one of these groups. 81 00:04:21,880 --> 00:04:25,960 OC Yeah, my font size is too small or too big. 82 00:04:25,990 --> 00:04:26,480 There we go. 83 00:04:26,500 --> 00:04:31,540 If I zoom out, we can see the longest page count here for each one of those authors. 84 00:04:32,110 --> 00:04:36,780 But remember, we're still only grouping on last name and we do have two heresies. 85 00:04:36,790 --> 00:04:44,950 So if I really wanted to be specific or accurate, I probably don't care about how many books have been 86 00:04:44,950 --> 00:04:46,690 written by people with the same last name. 87 00:04:46,690 --> 00:04:51,370 I want to know how many books have been written by each individual author, so I'm going to change my 88 00:04:51,370 --> 00:04:54,400 group by to be last name and also first name. 89 00:04:55,930 --> 00:05:02,890 And then maybe I'll also display the author first name so that we have an idea of which Harris is which. 90 00:05:03,520 --> 00:05:09,300 And then longest page count, let's just call it longest book or something or long. 91 00:05:09,310 --> 00:05:10,020 I don't know. 92 00:05:10,030 --> 00:05:15,430 Maybe I'll get rid of this part because it's just not going to display nicely with my screen size right 93 00:05:15,430 --> 00:05:16,030 now. 94 00:05:16,870 --> 00:05:18,250 Let's just leave it at that. 95 00:05:19,120 --> 00:05:19,960 And here we are. 96 00:05:20,080 --> 00:05:23,680 We see everything looks pretty much the same until we get down to Harris. 97 00:05:23,680 --> 00:05:25,150 We have Dan Harris. 98 00:05:25,150 --> 00:05:29,530 Here's his stats and Frieda Harris, here's her stats. 99 00:05:30,640 --> 00:05:32,620 So that is how we can use Min and Max. 100 00:05:32,620 --> 00:05:35,560 I think I have another example of mine and Max with Group VI. 101 00:05:35,590 --> 00:05:39,160 We already did this, find the longest page count for each author. 102 00:05:39,160 --> 00:05:45,280 We would group each author together, write all their rows together, and then find max pages just like 103 00:05:45,280 --> 00:05:45,760 that. 104 00:05:46,420 --> 00:05:47,020 All right. 105 00:05:47,020 --> 00:05:49,930 So next up, we've got another aggregate function.