1
00:00:00,210 --> 00:00:00,990
All right.

2
00:00:00,990 --> 00:00:05,820
So I know you've been dying in suspense, waiting to see how you use men and Macs in conjunction with

3
00:00:05,820 --> 00:00:06,510
Group B.

4
00:00:06,570 --> 00:00:07,830
Well, it's finally time.

5
00:00:08,100 --> 00:00:10,410
So the way we do it is really similar.

6
00:00:10,680 --> 00:00:13,570
It's basically identical to how we did count.

7
00:00:13,590 --> 00:00:15,710
When we use that along with group B.

8
00:00:16,230 --> 00:00:18,750
So let's take an example problem first.

9
00:00:19,080 --> 00:00:20,130
Something like this.

10
00:00:20,580 --> 00:00:24,060
Find the year that each author published their first book.

11
00:00:24,850 --> 00:00:31,330
So we want something like, I don't know, Raymond Carver and his I don't actually know this, but let's

12
00:00:31,330 --> 00:00:37,300
say that in our database, his first book was published in sometime in the eighties, I believe 1981,

13
00:00:37,300 --> 00:00:37,930
let's say.

14
00:00:38,950 --> 00:00:43,840
And we want to do the same thing for, let's say, David Foster Wallace.

15
00:00:43,840 --> 00:00:48,160
And his first book in our database was published in 1999, or whatever it is.

16
00:00:48,190 --> 00:00:49,540
Don't quote me, please.

17
00:00:50,080 --> 00:00:56,290
And so to do that, basically what we're doing is finding the minimum released year.

18
00:00:57,060 --> 00:01:09,210
But we can't just do this right where we do a select min released here from books because that only

19
00:01:10,020 --> 00:01:11,670
gives us the absolute minimum.

20
00:01:11,670 --> 00:01:15,240
We want to group all of our books by author.

21
00:01:15,630 --> 00:01:20,490
So unique author and then find the minimum release year for each one of those.

22
00:01:21,060 --> 00:01:24,780
So we need to use group buy and min and it looks like this.

23
00:01:26,240 --> 00:01:32,480
And yes, I know it looks kind of long as this emoji tells you it might be a little confusing.

24
00:01:33,080 --> 00:01:40,430
Basically, let's focus on the important parts first, which is this right here and then the group by

25
00:01:40,430 --> 00:01:41,090
statement.

26
00:01:41,120 --> 00:01:47,570
Now, remember, when we do group by I'm doing author l name, comma, author f name, because we have

27
00:01:47,570 --> 00:01:49,850
two authors who have the same last name.

28
00:01:49,850 --> 00:01:53,590
And so I don't want I believe it's Frieda Harris and Dan Harris.

29
00:01:53,600 --> 00:01:55,250
I don't want their books grouped together.

30
00:01:55,250 --> 00:01:56,690
So we're separating it out.

31
00:01:56,870 --> 00:02:01,280
So this is going to group all of our authors together make those different groups.

32
00:02:01,280 --> 00:02:04,220
I call them what I call them super rows or something like that.

33
00:02:04,730 --> 00:02:11,480
And then for each one of those, we're going to find the minimum release here, as well as the author,

34
00:02:11,480 --> 00:02:15,560
first name and last name, because if we just find the minimum release here, it's not that helpful

35
00:02:15,560 --> 00:02:17,540
to see if that's all we print out.

36
00:02:17,540 --> 00:02:23,240
We want something like Raymond Carver, 1980, rather than just seeing 1980.

37
00:02:23,750 --> 00:02:27,260
So let's give it a shot, make some space.

38
00:02:27,710 --> 00:02:39,200
We'll do a select author F name, comma, author, l name, comma, min released year, and then I'll

39
00:02:39,200 --> 00:02:51,020
do it on a separate line from books group by and we want to do that double author l name comma author

40
00:02:51,410 --> 00:02:52,070
f name.

41
00:02:52,910 --> 00:02:53,720
Let's hit enter.

42
00:02:55,290 --> 00:02:56,010
Here we go.

43
00:02:57,120 --> 00:03:03,090
So we got Raymond Carver and the minimum released here for him is 1981, which I think is what I just

44
00:03:03,090 --> 00:03:06,300
said or what I guessed, Don.

45
00:03:06,360 --> 00:03:14,280
Let's say Dave Eggers and we've got 2001 David Foster Wallace, 2000 for Neil Gaiman, 2001 and so on.

46
00:03:14,280 --> 00:03:17,940
We can see the minimum released year for each author.

47
00:03:18,450 --> 00:03:22,620
And just to hit this twice.

48
00:03:23,700 --> 00:03:26,220
Well, that's annoying by doing it on separate lines.

49
00:03:26,310 --> 00:03:37,140
If I had left off let me finish this from books group by and let's just say author l name if I just

50
00:03:37,140 --> 00:03:41,220
left it that everything looks the same except for our two Harrises.

51
00:03:41,220 --> 00:03:45,300
Dan Harris 2014 Frieda Harris 2001.

52
00:03:45,450 --> 00:03:51,000
Well, now all we have is Dan Harris in 2001, which is not true.

53
00:03:51,030 --> 00:03:53,010
He never released a book in 2001.

54
00:03:53,580 --> 00:03:54,780
Frieda Harris did.

55
00:03:54,780 --> 00:03:57,630
And that's because we're grouping everything by Harris.

56
00:03:57,630 --> 00:03:59,430
And so it puts Dan and Frida together.

57
00:03:59,430 --> 00:04:01,370
So that's why we need both.

58
00:04:01,380 --> 00:04:08,430
And so we're going to continue using that group by author L Name Comma Author for F name quite a bit

59
00:04:08,550 --> 00:04:12,450
as we progressed through these group buys in the next couple of videos.

60
00:04:13,860 --> 00:04:18,690
So we can also do something like find the longest page count for each author.

61
00:04:18,750 --> 00:04:20,070
It's very, very similar.

62
00:04:20,370 --> 00:04:21,870
We just need to use Max.

63
00:04:21,870 --> 00:04:33,780
So that's going to be a select max pages from books group by and then our same author l name comma author

64
00:04:33,810 --> 00:04:35,160
f name just like that.

65
00:04:35,160 --> 00:04:38,520
However, we just do that and it's not very useful.

66
00:04:38,880 --> 00:04:42,030
So let's go and add in select.

67
00:04:43,390 --> 00:04:49,210
Author F name, comma, author, ll name, comma, max pages.

68
00:04:49,450 --> 00:04:53,530
Now we can see Raymond Carver's longest book is 526.

69
00:04:54,500 --> 00:04:56,660
Neil Gaiman's is for 65.

70
00:04:56,690 --> 00:04:58,510
John Steinbeck is 181.

71
00:04:58,520 --> 00:05:00,350
He only has one book in there, I believe.

72
00:05:01,040 --> 00:05:03,220
George Saunders It's 367.

73
00:05:03,230 --> 00:05:05,080
It's as long as pages.

74
00:05:05,090 --> 00:05:08,560
So that's the basics of men and Macs in conjunction with groups.

75
00:05:09,260 --> 00:05:11,120
Obviously you could group things differently.

76
00:05:11,130 --> 00:05:15,650
We've been doing a lot of grouping based off of authors, but we could group things by the year they

77
00:05:15,650 --> 00:05:23,420
were released or I don't know, the quantity we have in stock or whatever we have whatever available

78
00:05:23,420 --> 00:05:25,090
piece of data we could group by.

79
00:05:25,100 --> 00:05:29,990
But grouping by author makes a lot of sense for calculating some sort of meaningful data.

80
00:05:30,710 --> 00:05:35,270
Next up, we're going to continue on with some of these aggregate functions, and we're going to in

81
00:05:35,270 --> 00:05:41,840
particular focus on some which will allow us to add things together and average, which allows us to

82
00:05:41,840 --> 00:05:42,650
calculate averages.

83
00:05:42,680 --> 00:05:43,790
Oh, wait, wait, wait.

84
00:05:44,330 --> 00:05:45,740
I forgot I made this slide.

85
00:05:45,740 --> 00:05:48,260
I ended the recording and everything started editing.

86
00:05:48,260 --> 00:05:49,460
And then I remembered.

87
00:05:49,460 --> 00:05:56,870
I also have this slide here of a nice cleaned up version that just shows you can use things like Macs

88
00:05:56,870 --> 00:05:58,970
and Group B along with King Cat.

89
00:05:59,090 --> 00:06:02,180
So all that this does is same thing.

90
00:06:02,180 --> 00:06:07,700
It finds the longest page count for each author, but rather than just printing out this mess here,

91
00:06:07,880 --> 00:06:09,050
it's not really even a mess.

92
00:06:09,050 --> 00:06:16,880
But what it does is it combines some data and it uses aliases so that we have author and longest book

93
00:06:16,880 --> 00:06:22,520
as our column headers and can cat to put together as you've already seen the author's full name.

94
00:06:23,240 --> 00:06:25,460
So just copy that.

95
00:06:26,480 --> 00:06:30,020
Run over here, paste it and there we go.

96
00:06:30,140 --> 00:06:33,200
We get author longest book and we have a full name here.

97
00:06:33,200 --> 00:06:34,370
And their longest book.

98
00:06:34,760 --> 00:06:36,320
Yep, that's it.

99
00:06:36,860 --> 00:06:39,440
Now we're actually done, I promise.