1
00:00:00,090 --> 00:00:05,820
Next up, we're talking about Avg, avg.

2
00:00:06,210 --> 00:00:06,780
Hang on.

3
00:00:07,530 --> 00:00:09,570
Next up, we're talking about average.

4
00:00:09,570 --> 00:00:13,590
And as you might have guessed, because I've said this for pretty much every video in this section,

5
00:00:13,590 --> 00:00:14,970
it does what it sounds like.

6
00:00:14,990 --> 00:00:15,990
Actually, that's a lie.

7
00:00:16,020 --> 00:00:20,160
It sounds like AVG, but what it does is average data.

8
00:00:20,160 --> 00:00:25,290
So it will some things together and then divide them based off of how many things it added together.

9
00:00:25,860 --> 00:00:30,930
So it's pretty useful if you're trying to do things like find the, I don't know, average sales for

10
00:00:30,930 --> 00:00:37,300
something in a given month or find the average price of something in a database or the average I don't

11
00:00:37,300 --> 00:00:37,890
know anything.

12
00:00:37,920 --> 00:00:42,670
The average number of users that are the average number of followers that your YouTube videos have.

13
00:00:42,690 --> 00:00:45,890
I mean, averages are pretty useful in general.

14
00:00:45,900 --> 00:00:48,750
Think of all the times in your life, hopefully.

15
00:00:48,750 --> 00:00:52,830
I mean, I don't know, maybe you don't maybe you never average things, but I feel like it's something

16
00:00:52,830 --> 00:00:54,050
I find myself doing.

17
00:00:54,060 --> 00:00:58,450
It's a lot of what teachers do is find averages, average scores, average grades.

18
00:00:58,480 --> 00:00:58,890
Okay.

19
00:00:58,950 --> 00:01:00,580
Well, let's just get back to this.

20
00:01:00,600 --> 00:01:02,880
There are a lot of uses for averages.

21
00:01:03,120 --> 00:01:04,650
There's not a perfect way of getting insight.

22
00:01:04,650 --> 00:01:11,070
I don't want to say that the average is this amazing, all encompassing piece of data, but it can't

23
00:01:11,070 --> 00:01:12,180
be useful anyways.

24
00:01:12,180 --> 00:01:13,830
So how do we use it?

25
00:01:14,250 --> 00:01:17,670
Also, as you might have guessed, I'm going to show you how to use it on its own first and then how

26
00:01:17,670 --> 00:01:18,860
to use it with group buy.

27
00:01:18,890 --> 00:01:25,350
By now we'll be able to do this pretty quickly and it should serve as review so we could do something

28
00:01:25,350 --> 00:01:25,950
like this.

29
00:01:25,950 --> 00:01:28,890
Calculate the average released year across all books.

30
00:01:29,790 --> 00:01:31,110
And that's very simple.

31
00:01:31,170 --> 00:01:38,100
We do a select AVG average release here from books.

32
00:01:39,560 --> 00:01:44,630
And as you can see, we get 1990 9.7895.

33
00:01:45,320 --> 00:01:49,550
And it's important to note, yes, there is a decimal and it goes out to four places here.

34
00:01:50,350 --> 00:01:55,000
We'll talk more about different types of data data types in a coming section.

35
00:01:55,000 --> 00:01:59,620
We'll we'll talk about the differences between integers and the ways of storing decimals.

36
00:01:59,620 --> 00:02:05,320
But for now, it's important that you notice when you do average, it doesn't round things up to a whole

37
00:02:05,320 --> 00:02:06,010
number.

38
00:02:06,220 --> 00:02:07,960
It gives you four decimal points.

39
00:02:07,960 --> 00:02:16,360
We could also do things like select the average page count from all of our books, which is 348.57,

40
00:02:16,360 --> 00:02:17,410
eight, nine pages.

41
00:02:18,040 --> 00:02:18,270
Okay.

42
00:02:18,580 --> 00:02:23,020
So now let's see how to use it with group B, that's a more common application.

43
00:02:23,800 --> 00:02:29,950
How do we do something like calculate the average stock quantity for books released in the same year?

44
00:02:30,010 --> 00:02:31,810
So this is a little bit different.

45
00:02:31,810 --> 00:02:33,850
We're not grouping by author in this case.

46
00:02:33,850 --> 00:02:38,650
We have a couple of books that were were released in the same year by different authors.

47
00:02:38,650 --> 00:02:42,010
And this is sort of arbitrary, very arbitrary.

48
00:02:42,010 --> 00:02:46,720
But how do we calculate the average stock quantity for books released in the same year?

49
00:02:47,950 --> 00:02:54,640
Looks like this select avg average stock quantity from books grouped by release year.

50
00:02:54,820 --> 00:02:55,930
So let's try it.

51
00:02:56,920 --> 00:03:09,790
Select average and we want stock quantity quantity from books group by released year.

52
00:03:11,320 --> 00:03:12,190
Just like that.

53
00:03:13,210 --> 00:03:19,040
Not that insightful to look at, let's be honest, but maybe we could do something like this.

54
00:03:19,060 --> 00:03:20,650
Let's print out the release year.

55
00:03:20,680 --> 00:03:25,060
The count how many books were released that year and then the average year.

56
00:03:26,020 --> 00:03:28,390
So it will look like the average stock quantity.

57
00:03:28,390 --> 00:03:29,020
Excuse me.

58
00:03:29,020 --> 00:03:32,500
So we'll have released year.

59
00:03:34,980 --> 00:03:40,560
And then let's also well, let's just leave it at that for at least a year and the average stock quantity.

60
00:03:40,590 --> 00:03:48,210
And if I do that, you can see 1945, 95 is our average quantity.

61
00:03:48,570 --> 00:03:51,720
2134 is your average quantity?

62
00:03:51,930 --> 00:03:52,530
Yes.

63
00:03:52,530 --> 00:03:53,610
Not that useful?

64
00:03:53,610 --> 00:03:55,290
I totally agree.

65
00:03:55,290 --> 00:04:00,960
But I just want to mix it up because we've been doing a lot of grouped by author L name, comma, author

66
00:04:00,960 --> 00:04:06,060
F name and it gets kind of old so we can group buy other important pieces of data.

67
00:04:06,720 --> 00:04:09,450
But of course we could do something like this.

68
00:04:09,750 --> 00:04:18,390
Select Let's do our good old author f name, author l name and average.

69
00:04:18,390 --> 00:04:27,270
Let's do average pages written by every author or each author from books group by author.

70
00:04:27,270 --> 00:04:27,840
L Name.

71
00:04:27,870 --> 00:04:29,820
Author f name.

72
00:04:31,350 --> 00:04:32,030
Oh, boy.

73
00:04:32,030 --> 00:04:33,650
She's a little bit too eager there.

74
00:04:33,680 --> 00:04:34,730
What's our problem?

75
00:04:36,830 --> 00:04:38,840
Looks like I'm missing a comma.

76
00:04:40,390 --> 00:04:41,170
Right here.

77
00:04:44,290 --> 00:04:47,140
So you can see the average page is written.

78
00:04:47,140 --> 00:04:51,340
We saw how to select the min and the max as well as the sum.

79
00:04:51,460 --> 00:04:52,870
Now we have average.

80
00:04:53,620 --> 00:05:00,550
Also, it's important to note that someone like let's see John Steinbeck, who we know has one book,

81
00:05:00,580 --> 00:05:04,780
181, it still adds four decimal points.

82
00:05:04,780 --> 00:05:07,120
And that has to do with the data type that this is.

83
00:05:07,660 --> 00:05:11,140
And basically, I'm just going to punt that one until we talk about data types.

84
00:05:11,140 --> 00:05:12,670
But I just want to highlight it here.

85
00:05:12,670 --> 00:05:19,540
You still get those four decimal points, even if it's an even integer, even not meaning.

86
00:05:19,930 --> 00:05:24,100
That's a bad choice of words, not as in even an odd, let's say, as a whole number.

87
00:05:24,100 --> 00:05:24,730
There we go.

88
00:05:25,360 --> 00:05:25,750
All right.

89
00:05:25,750 --> 00:05:26,860
So that's average.

90
00:05:26,860 --> 00:05:32,320
And that actually wraps up the section on or the new content on these aggregate functions.

91
00:05:32,320 --> 00:05:40,240
So we saw count what we saw grouped by and then count min, max, sum and average all different things

92
00:05:40,240 --> 00:05:43,900
that we can do on their own or in conjunction with Group BI.

93
00:05:44,440 --> 00:05:44,800
All right.

94
00:05:44,800 --> 00:05:46,960
So now we're going to get another chance to practice it.

95
00:05:47,380 --> 00:05:49,000
Hopefully you're looking forward to that.

96
00:05:49,390 --> 00:05:51,820
And if you're not, it's still good to do.

97
00:05:51,820 --> 00:05:54,550
It's like, you know, vegetables just got to eat them.

98
00:05:55,870 --> 00:05:58,300
I hope I don't offend anyone if you're a vegetarian.

99
00:05:59,230 --> 00:05:59,860
All right.