All right.
So I know you've been dying in suspense, waiting to see how you use men and Macs in conjunction with
Group B.
Well, it's finally time.
So the way we do it is really similar.
It's basically identical to how we did count.
When we use that along with group B.
So let's take an example problem first.
Something like this.
Find the year that each author published their first book.
So we want something like, I don't know, Raymond Carver and his I don't actually know this, but let's
say that in our database, his first book was published in sometime in the eighties, I believe 1981,
let's say.
And we want to do the same thing for, let's say, David Foster Wallace.
And his first book in our database was published in 1999, or whatever it is.
Don't quote me, please.
And so to do that, basically what we're doing is finding the minimum released year.
But we can't just do this right where we do a select min released here from books because that only
gives us the absolute minimum.
We want to group all of our books by author.
So unique author and then find the minimum release year for each one of those.
So we need to use group buy and min and it looks like this.
And yes, I know it looks kind of long as this emoji tells you it might be a little confusing.
Basically, let's focus on the important parts first, which is this right here and then the group by
statement.
Now, remember, when we do group by I'm doing author l name, comma, author f name, because we have
two authors who have the same last name.
And so I don't want I believe it's Frieda Harris and Dan Harris.
I don't want their books grouped together.
So we're separating it out.
So this is going to group all of our authors together make those different groups.
I call them what I call them super rows or something like that.
And then for each one of those, we're going to find the minimum release here, as well as the author,
first name and last name, because if we just find the minimum release here, it's not that helpful
to see if that's all we print out.
We want something like Raymond Carver, 1980, rather than just seeing 1980.
So let's give it a shot, make some space.
We'll do a select author F name, comma, author, l name, comma, min released year, and then I'll
do it on a separate line from books group by and we want to do that double author l name comma author
f name.
Let's hit enter.
Here we go.
So we got Raymond Carver and the minimum released here for him is 1981, which I think is what I just
said or what I guessed, Don.
Let's say Dave Eggers and we've got 2001 David Foster Wallace, 2000 for Neil Gaiman, 2001 and so on.
We can see the minimum released year for each author.
And just to hit this twice.
Well, that's annoying by doing it on separate lines.
If I had left off let me finish this from books group by and let's just say author l name if I just
left it that everything looks the same except for our two Harrises.
Dan Harris 2014 Frieda Harris 2001.
Well, now all we have is Dan Harris in 2001, which is not true.
He never released a book in 2001.
Frieda Harris did.
And that's because we're grouping everything by Harris.
And so it puts Dan and Frida together.
So that's why we need both.
And so we're going to continue using that group by author L Name Comma Author for F name quite a bit
as we progressed through these group buys in the next couple of videos.
So we can also do something like find the longest page count for each author.
It's very, very similar.
We just need to use Max.
So that's going to be a select max pages from books group by and then our same author l name comma author
f name just like that.
However, we just do that and it's not very useful.
So let's go and add in select.
Author F name, comma, author, ll name, comma, max pages.
Now we can see Raymond Carver's longest book is 526.
Neil Gaiman's is for 65.
John Steinbeck is 181.
He only has one book in there, I believe.
George Saunders It's 367.
It's as long as pages.
So that's the basics of men and Macs in conjunction with groups.
Obviously you could group things differently.
We've been doing a lot of grouping based off of authors, but we could group things by the year they
were released or I don't know, the quantity we have in stock or whatever we have whatever available
piece of data we could group by.
But grouping by author makes a lot of sense for calculating some sort of meaningful data.
Next up, we're going to continue on with some of these aggregate functions, and we're going to in
particular focus on some which will allow us to add things together and average, which allows us to
calculate averages.
Oh, wait, wait, wait.
I forgot I made this slide.
I ended the recording and everything started editing.
And then I remembered.
I also have this slide here of a nice cleaned up version that just shows you can use things like Macs
and Group B along with King Cat.
So all that this does is same thing.
It finds the longest page count for each author, but rather than just printing out this mess here,
it's not really even a mess.
But what it does is it combines some data and it uses aliases so that we have author and longest book
as our column headers and can cat to put together as you've already seen the author's full name.
So just copy that.
Run over here, paste it and there we go.
We get author longest book and we have a full name here.
And their longest book.
Yep, that's it.
Now we're actually done, I promise.