WEBVTT 00:00.120 --> 00:07.080 Next up we're talking about as a good thing. 00:07.590 --> 00:09.550 Next up we're talking about average. 00:09.660 --> 00:13.920 And as you might have guessed because I've said this for pretty much every video on this section it 00:13.920 --> 00:15.020 doesn't it sounds like. 00:15.030 --> 00:16.010 Actually that's a lie. 00:16.020 --> 00:17.270 It sounds like. 00:17.520 --> 00:20.220 But what it does is average data. 00:20.220 --> 00:25.560 So it will some things together and then divide them based off of how many things added together. 00:25.980 --> 00:31.260 So it's pretty useful if you're trying to do things like find the I don't know average sales for something 00:31.260 --> 00:38.190 in a given month or find the average price of something in a database or the average anything an average 00:38.190 --> 00:42.630 number of users that are average number of followers that your YouTube videos have. 00:42.690 --> 00:45.840 I mean averages are pretty useful in general. 00:45.840 --> 00:47.760 Think of all the times in your life. 00:48.030 --> 00:48.750 Hopefully. 00:48.780 --> 00:50.050 I mean I don't know maybe you don't. 00:50.190 --> 00:54.040 Maybe you never average things but I feel like it's something I find myself doing. 00:54.060 --> 00:58.370 It's a lot of what teachers do is find averages average scores average grades. 00:58.510 --> 01:00.660 OK let's just get back to this. 01:00.690 --> 01:03.150 There are a lot of uses for averages. 01:03.150 --> 01:04.730 It's not a perfect way of getting inside it. 01:04.730 --> 01:11.130 I want to say that you know the average is this amazing all encompassing piece of data but it can be 01:11.130 --> 01:12.180 useful anyway. 01:12.240 --> 01:14.310 So how do we use it. 01:14.310 --> 01:17.700 Also as you might have guessed I'm going to show you how to use it on its own first and then how to 01:17.700 --> 01:19.010 use it with group type. 01:19.050 --> 01:25.320 By now we'll be able to do this pretty quickly and it should service review so we could do something 01:25.320 --> 01:25.950 like this. 01:26.040 --> 01:31.070 Calculate the average released year across all books and that's very simple. 01:31.230 --> 01:42.240 We do a select a Viji average release year from books and as you can see we get nineteen ninety nine 01:42.240 --> 01:44.870 point seven eight nine five. 01:45.480 --> 01:47.690 And it's important to note yes there is a decimal. 01:47.730 --> 01:49.580 And it goes out to four places here. 01:50.470 --> 01:54.960 We'll talk more about different types of data data types in a coming election. 01:55.060 --> 01:59.590 Well we'll talk about the differences between integers and the ways of storing decimals. 01:59.590 --> 02:05.260 But for now it's important that you notice when you do average it doesn't round things up to a whole 02:05.260 --> 02:06.220 number. 02:06.220 --> 02:08.060 It gives you four decimal points. 02:08.080 --> 02:16.030 You can also do things like select the average page count from all of our books which is 348 point five 02:16.030 --> 02:17.640 seven eight nine pages. 02:18.190 --> 02:18.580 OK. 02:18.670 --> 02:20.860 So now let's see how to use it with Group. 02:20.980 --> 02:23.890 That's a more common application. 02:23.890 --> 02:29.900 How do we do something like calculate the average stock quantity for books released in the same year. 02:30.100 --> 02:31.840 So this is a little bit different. 02:31.840 --> 02:33.940 We're not grouping by author in this case. 02:34.000 --> 02:40.450 We have a couple of books that were released in the same year by different authors and this is sort 02:40.450 --> 02:42.000 of arbitrary and very arbitrary. 02:42.010 --> 02:46.920 But how do we calculate the average stock quantity for books released in the same year. 02:47.950 --> 02:54.700 Looks like this select e.g. average stock quantity from books group by release year. 02:54.970 --> 02:56.830 So let's try it. 02:57.010 --> 03:06.640 Select average and we want to stock quantity quantity from books. 03:06.640 --> 03:13.090 GROUP BY released here just like that. 03:13.330 --> 03:15.160 Not that insightful to look at. 03:15.400 --> 03:16.410 Let's be honest. 03:16.840 --> 03:19.090 But maybe we could do something like this. 03:19.180 --> 03:25.410 Let's print out the release year that count how many books were released say you're in an average year. 03:26.140 --> 03:34.600 So it will look like the average stock quantity excuse me so we'll have released year. 03:35.120 --> 03:40.580 And then let's also well let's just leave it at that at least and an average stock quantity. 03:40.730 --> 03:51.790 And if I do that you can see 1945 95 or average quantity hundred 2001 134 is your average quantity. 03:52.090 --> 03:52.550 Yes. 03:52.570 --> 03:53.620 Not that useful. 03:53.620 --> 03:55.230 I totally agree. 03:55.480 --> 04:01.360 But I just want to mix it up because we've been doing a lot of group by author name comma author name 04:01.420 --> 04:06.240 and it gets kind of old so we can group by other important pieces of data. 04:06.820 --> 04:09.690 But of course we could do something like this. 04:09.890 --> 04:20.050 Select Let's do our good old author name author Al name and average Let's do average pages written by 04:20.050 --> 04:30.530 every author or each author from books group by author L. name author F. name. 04:31.380 --> 04:32.060 Ooh boy. 04:32.120 --> 04:33.690 A little bit too eager there. 04:33.730 --> 04:35.170 What's her problem. 04:36.830 --> 04:38.890 Looks like I'm missing a comma. 04:40.390 --> 04:41.200 Right here. 04:43.650 --> 04:44.190 OK. 04:44.420 --> 04:47.100 So you can see the average page is written. 04:47.270 --> 04:51.650 We saw how to select the men in the Macs as well as the sum. 04:51.650 --> 04:53.410 Now we have average. 04:53.690 --> 05:01.490 Also it's important to note that someone like let's see John Steinbeck who we know has one book 181 05:02.740 --> 05:04.800 it still adds the four decimal points. 05:04.820 --> 05:07.670 That has to do with the data type that this is. 05:07.760 --> 05:12.200 And basically I understand punt that one we talk about data types but I just wanted to highlight it 05:12.200 --> 05:12.720 here. 05:12.800 --> 05:19.480 You still get those four decimal points even if it's an even integer even not meaning. 05:19.560 --> 05:22.320 Ok that's a bad choice of words not as an even and odd. 05:22.520 --> 05:24.920 Let's say as a whole number. 05:25.400 --> 05:26.850 All right so that's average. 05:26.870 --> 05:32.330 And that actually wraps up the section on the new content on these aggregate functions. 05:32.480 --> 05:40.310 So we saw count what we saw grouped by and then count min max sum an average all different things that 05:40.310 --> 05:44.070 we can do on their own or in conjunction with Group By. 05:44.480 --> 05:47.120 All right so now we're going to get another chance to practice it. 05:47.420 --> 05:49.320 Hopefully you're looking forward to that. 05:49.460 --> 05:51.810 And if you're not it's still good to do. 05:51.840 --> 05:55.910 It's like you know vegetables just gotta eat them. 05:55.910 --> 05:57.110 I hope I don't offend anyone. 05:57.140 --> 05:59.650 If you're vegetarian right.