WEBVTT 00:00.190 --> 00:01.080 All right. 00:01.080 --> 00:06.480 So I know you've been dying in suspense waiting to see how you use min and max in conjunction with goodbye. 00:06.620 --> 00:08.080 Well it's finally time. 00:08.250 --> 00:10.490 So the way we do it is really similar. 00:10.670 --> 00:15.930 It's basically identical to how we did count when we use that along with GROUP BY. 00:16.380 --> 00:17.980 So let's take an example problem. 00:18.000 --> 00:25.750 First something like this find the year that each author published their first book so we want something 00:25.750 --> 00:32.950 like Raymond Carver and his that I don't actually know this but let's say that in our database his first 00:32.950 --> 00:39.030 book was published in sometime in the 80s I believe 1980 one let's say. 00:39.100 --> 00:45.910 And we do the same thing for let's say David Foster Wallace in his first book in our database was published 00:45.910 --> 00:48.260 in 1999 or whatever it is. 00:48.310 --> 00:50.170 Don't quote me please. 00:50.180 --> 00:52.540 And so to do that. 00:52.690 --> 01:00.520 Basically what we're doing is finding the minimum release year but we can't just do this right where 01:00.530 --> 01:12.080 we do a select men released year from books because that only gives us the absolute minimum we want 01:12.080 --> 01:13.070 a group. 01:13.250 --> 01:21.100 All of our books by author so unique author and then find the minimum release year for each one of those. 01:21.170 --> 01:23.640 So we need to use GROUP BY MEN. 01:23.720 --> 01:26.270 And it looks like this. 01:26.370 --> 01:33.040 And yes I know this looks kind of long as this emoji tells you it might be a little confusing. 01:33.200 --> 01:38.310 Basically let's focus on the important parts first which is this right here. 01:39.490 --> 01:41.320 And then the group by statement. 01:41.320 --> 01:48.580 Now remember when we do a group by I'm doing author L name Khama author name because we have two authors 01:48.580 --> 01:49.910 who have the same last name. 01:50.020 --> 01:53.590 And so I don't want I believe it's free to Harris and Dan Harris. 01:53.640 --> 01:55.240 I don't want their books grouped together. 01:55.420 --> 01:57.000 So we're separating it out. 01:57.040 --> 02:01.900 So this is going to group all of her authors together make those different groups I call them what I 02:01.900 --> 02:04.720 call super rose or something like that. 02:04.840 --> 02:11.440 And then for each one of those We're going to find the minimum release year as well as the author's 02:11.440 --> 02:15.970 first name and last name because if we just find the minimum release here it's not that helpful to see 02:16.480 --> 02:17.570 if that's all we print out. 02:17.590 --> 02:23.480 We want something like Raymond Carver 1980 rather than just seeing 1980. 02:23.890 --> 02:25.590 So let's give it a shot. 02:26.230 --> 02:27.600 Make some space. 02:27.780 --> 02:39.490 We'll do a select author half name com author Al name Kalima men released here and then I'll do it on 02:39.490 --> 02:44.860 a separate line from books grouped by. 02:45.370 --> 02:57.050 And we want to do that double author L name comma author Daphne enter it we go. 02:57.150 --> 03:03.330 So we got Raymond Carver and the minimum release here for him is 1981 which I think is what I just said 03:03.480 --> 03:10.270 or what I guessed Don let's say Dave Eggers and we've got two dozen one. 03:10.440 --> 03:13.460 David Foster Wallace 2004 no game in 2001. 03:13.470 --> 03:18.290 And so on we can see the minimum released year for each author. 03:18.600 --> 03:23.440 And just to you know hit twice. 03:23.660 --> 03:24.630 Well that's annoying. 03:24.800 --> 03:26.240 Or doing it on separate lines. 03:26.490 --> 03:29.280 If I had left off. 03:29.430 --> 03:32.320 Let me finish this from books. 03:32.700 --> 03:36.800 Group by and let's just say author Al name. 03:36.810 --> 03:43.680 I just left it that everything looks the same except for two Harris's Dan Harris 2014 Frieda Harris 03:44.220 --> 03:45.340 2001. 03:45.630 --> 03:53.470 Well now all we have is Dan Harris in 2001 which is not true he never released a book in 2001. 03:53.580 --> 03:54.780 Frita Harris did. 03:55.020 --> 03:57.640 And that's because we're grouping everything by Harris. 03:57.720 --> 03:59.500 And so it puts Dan and Frita together. 03:59.580 --> 04:01.340 So that's why we need both. 04:01.590 --> 04:08.350 And so we're going to continue using that group by author L. name Khama author for name quite a bit. 04:08.550 --> 04:12.500 As we progressed through these group buys and the next couple of videos. 04:12.760 --> 04:13.450 OK. 04:14.010 --> 04:18.780 So we can also do something like find the longest page count for each author. 04:18.820 --> 04:20.450 Very very similar. 04:20.520 --> 04:21.960 We just need to use max. 04:21.990 --> 04:30.710 So that's going to be a select max pages from books group by. 04:30.930 --> 04:35.340 And then our same author name Khama author FNMA just like that. 04:35.340 --> 04:39.030 However we just do that and it's not very useful. 04:39.030 --> 04:49.300 So let's go and add in select author F. name Khama author and then come back pages. 04:49.630 --> 04:54.430 Now we can see Raymond Carver's longest book is 526. 04:54.500 --> 04:56.720 Neil Gaiman's is for 65. 04:56.750 --> 04:58.520 John Steinbeck is 181. 04:58.520 --> 05:00.920 He only has one book in there I believe. 05:01.040 --> 05:05.060 George Saunders is 367 his longest pages. 05:05.270 --> 05:09.010 So that's the basics of min and max in conjunction with Group II. 05:09.350 --> 05:11.150 Obviously you could group things differently. 05:11.150 --> 05:15.740 We've been doing a lot of grouping based off of authors but we could group things by the year they were 05:15.740 --> 05:23.660 released or I don't know the quantity we have in stock or whatever we have whatever available piece 05:23.660 --> 05:29.450 of data we could get by but grouping by author makes a lot of sense for calculating some sort of meaningful 05:29.450 --> 05:30.300 data. 05:30.830 --> 05:35.750 Next up we're going to continue on with some of these aggregate functions and we're going to in particular 05:35.750 --> 05:42.620 focus on some which will allow us to add things together and average which allows us to calculate averages 05:42.680 --> 05:48.560 Oh wait wait wait I forgot I made this slide I ended the recording and everything started editing and 05:48.560 --> 05:55.640 then I remembered I also have this slide here of a nice clean up version that just shows you can use 05:55.640 --> 05:59.190 things like Max and grouped by along with Concat. 05:59.270 --> 06:05.930 So all this does is same thing it finds the longest page count for each author but rather than just 06:05.930 --> 06:13.490 printing out this mess here it's not really even a mess but what it does is it combines some data and 06:13.490 --> 06:20.300 it uses aliases so that we have author and longest book as our column headers and Kinkead to put together 06:20.330 --> 06:23.340 as you've already seen the author's full name. 06:23.360 --> 06:30.220 So just copy that one over here paste it and there we go. 06:30.320 --> 06:34.530 We get author longest book and we have a full name here and there longest book. 06:34.880 --> 06:36.950 Yep that's it. 06:36.980 --> 06:38.130 Now we're actually done. 06:38.760 --> 06:39.220 I promise.