All right.
So I know you've been dying in suspense waiting to see how you use min and max in conjunction with goodbye.
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 BY.
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 Raymond Carver and his that I don't actually know this but let's say that in our database his first
book was published in sometime in the 80s I believe 1980 one let's say.
And we do the same thing for let's say David Foster Wallace in 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 release year but we can't just do this right where
we do a select men released year from books because that only gives us the absolute minimum we want
a 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 BY MEN.
And it looks like this.
And yes I know this 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 a group by I'm doing author L name Khama author name because we have two authors
who have the same last name.
And so I don't want I believe it's free to 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 her authors together make those different groups I call them what I
call super rose or something like that.
And then for each one of those We're going to find the minimum release year as well as the author's
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 half name com author Al name Kalima men released here and then I'll do it on
a separate line from books grouped by.
And we want to do that double author L name comma author Daphne enter it we go.
So we got Raymond Carver and the minimum release 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 two dozen one.
David Foster Wallace 2004 no game in 2001.
And so on we can see the minimum released year for each author.
And just to you know hit twice.
Well that's annoying.
Or doing it on separate lines.
If I had left off.
Let me finish this from books.
Group by and let's just say author Al name.
I just left it that everything looks the same except for two Harris's 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.
Frita Harris did.
And that's because we're grouping everything by Harris.
And so it puts Dan and Frita together.
So that's why we need both.
And so we're going to continue using that group by author L. name Khama author for name quite a bit.
As we progressed through these group buys and the next couple of videos.
OK.
So we can also do something like find the longest page count for each author.
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 name Khama author FNMA 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 Khama author and then come back 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 is 367 his longest pages.
So that's the basics of min and max in conjunction with Group II.
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 get 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 clean up version that just shows you can use
things like Max and grouped by along with Concat.
So all 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 Kinkead to put together
as you've already seen the author's full name.
So just copy that one over here paste it and there we go.
We get author longest book and we have a full name here and there longest book.
Yep that's it.
Now we're actually done.
I promise.