All right.
Returning to Min and Max, these built in functions, we saw that we can use them on their own without
any grouping, at least any of our own grouping.
But we can also use them in conjunction with Group VI.
In the same way we can use count on its own or with group by so we can do things like this.
Find the year each author published their first book.
In other words, group all of the books by the same authors together and then find the minimum year
for that author.
So we're not finding the minimum year of the entire table.
We know how to do that, but find the minimum year for each author.
And that would look like this.
Select whatever we want, the first name, the last name, and then the minimum released year from books.
And then we can group by.
In this case, I'm grouping by first name and last name because we do have some duplicated as we've
already seen.
There's two heresies, but we could also just keep it simple to start and just grouped by the author's
last name.
So let's take a look at that.
Let's do I'll write it in a script here.
Select let's do author first, let's do last name from books.
We'll group by author last name.
So that will make us our groups.
And what I want to find is not the count that's going to tell me how many books that author has written.
I want to know what is the minimum released year within each one of those groups?
Let's check it out.
Well, it looks like Lahiri, her youngest book or her first book in the data set at least, is 96.
Steinbeck only has one.
Eggers I know has three books.
The first one is 2001.
Who else is in here?
Saunders 2017.
Gaiman has three books, I think two or three in the data set.
2001 is his earliest book, so we could tweak it and do maximum year.
That would be the latest book that they wrote.
And there we are.
We see here a 2003 game in 2016.
Eggers 2013.
We could even do both.
Right.
I could do Min and Max released least.
Let's try that.
And now we see that Lahiri has two books.
One is from.
Well, this isn't.
Tell us that there's two, but there's at least two.
The minimum released here is 96.
Maximum is 2003.
Eggers 2013 is the maximum.
2001 is the minimum.
And that's the basic concept here.
We formed our groups and then we can, within each group, find the minimum or the max or the count.
I could throw that in here too.
There's no reason I can't.
Let me just split this up a bit, maybe on a second line for each author that we've grouped by their
last name, we can find the last name, their maximum release here, the minimum release year.
How about we find the number of books written So that would be Count Star.
And why don't we alias each one of these so as books?
Written.
And then.
How about we call this one as earliest release?
And this is getting quite long, so I'm going to do this on different lines.
We can format this later, but we'll start like this.
I'll indent it.
So we have our last name.
Put that one down.
The count for each of these groups.
We'll call.
We'll call it books written the maximum released year as there.
That should actually be latest release.
And then minimum release year will be earliest release.
Let's check it out, paste it over here, or just run it from within one of the GIs.
And we see Lahiri has two books.
Latest release 2003 Earliest release 96.
Eggers has three books.
2013 is the latest, 2001 is the earliest.
And let's add one more piece in.
Let's find the longest page count.
Right.
So we could do maximum of pages as longest page count or something like that.
And again, that is for each one of these groups.
OC Yeah, my font size is too small or too big.
There we go.
If I zoom out, we can see the longest page count here for each one of those authors.
But remember, we're still only grouping on last name and we do have two heresies.
So if I really wanted to be specific or accurate, I probably don't care about how many books have been
written by people with the same last name.
I want to know how many books have been written by each individual author, so I'm going to change my
group by to be last name and also first name.
And then maybe I'll also display the author first name so that we have an idea of which Harris is which.
And then longest page count, let's just call it longest book or something or long.
I don't know.
Maybe I'll get rid of this part because it's just not going to display nicely with my screen size right
now.
Let's just leave it at that.
And here we are.
We see everything looks pretty much the same until we get down to Harris.
We have Dan Harris.
Here's his stats and Frieda Harris, here's her stats.
So that is how we can use Min and Max.
I think I have another example of mine and Max with Group VI.
We already did this, find the longest page count for each author.
We would group each author together, write all their rows together, and then find max pages just like
that.
All right.
So next up, we've got another aggregate function.