Next up, we move on to one of the most useful and most important clauses in all of SQL, which is grouped
by group.
It also happens to be a little bit confusing up front.
Or it can be when you're starting out, it's not terrible or anything.
It's just conceptually different than anything we've done so far.
So take a deep breath with me.
Stay.
Stay in there.
Hang in there.
We're going to make it.
It will be fine.
It's not that bad.
I feel like I'm scaring you.
But here is the definition from the docs grouped by summarizes or aggregates identical data into single
rows.
So what do I mean by that?
Well, here's an example of how we could use it.
I'll come back to this.
But Group VI and then some column is going to group internally.
It's going to group the rows from our books table based upon author L name.
So if this is our table, I've simplified it drastically.
We have what, three different authors?
If I said to SQL, please group all of these rows by author last name.
Don't worry about what it prints out or anything just yet, but you can visualize it this way in memory.
It makes a group with all the books, all the rows written by Lahiri, and it also makes a group that
has the game in rows together and a final group that has the Edgars rows.
It's only one row, so this is not what's printed out.
I want to be very clear about that.
This is just what is happening behind the scenes.
In fact, if I try this right now, let's do first, let's just do select author.
L name from books.
No grouping.
Of course, we see all the duplicates.
And if I instead say select author l name from books group by author L name.
This is what we see.
Just every every author's name appears once, but this doesn't show us what's happening behind the scenes.
As I said, in memory, out of out of view in the code.
Essentially, there are these groups that are formed and we can ask questions or query things about
those individual groups.
And that's where it becomes useful because right now I've basically done the same thing.
It's just asking for the distinct author names, last names rather, but actually behind the scenes
there's a lot more going on.
So instead of just grouping them and then just selecting the author name after they've been grouped,
what if I did something like this?
What if we count how many books each author has written?
So if you believe me that there are these groups behind the scenes grouped by the author last name,
I should then be able to use count to find out how many rows exist in each group.
And that's exactly what I'm going to do.
So this query here says, Let's start at the end group all of our rows together by the author last name.
So imagine we have these little mini groups all over the place, and then we say we want to select the
author last name from each of those groups and of course, each group that has the same author last
name.
They have to because that's how they're grouped.
So we take that name like Lahiri and then Count Star.
And as we just saw, Count Star means count the rows.
And in this case, it's going to count the rows of each group, not all the rows in the whole table,
but the rows in each group.
So let's see what that might end up with.
We might see two for Lahiri, two for Gaiman, and one for Eggers.
And that's based off of this fiction or this tiny data set from my slides.
And the end result would look something like this.
Lahiri To Gaiman.
To Eggers one.
So let's try it for real over here.
Instead of selecting author l name from books grouped by author L name, I'm going to keep all of that,
but after my author L name or before doesn't matter.
But I'm going to select the author name first, so that will appear on the left.
Then Count Star.
And once again, the way that this works is that we have grouped by the author's last name.
So there's all these mini groupings and then we count the rows in each of those mini groups and we see
Lahiri appears twice, Garman appears three times, Edgar's three times.
And I could even get fancier and do something like count and let's give it a name, like as books written.
And then why don't we sort by that?
So I'll get rid of my semicolon order by books written.
And what did I mess up here?
I think I have a missing space.
Possibly.
There we go.
And I kind of want it to be in descending order.
And here we are.
We have each author in our dataset and the number of books that they wrote.
It's not exactly accurate because.
Harris If you remember, there's two Harris's the same last name, so we're only grouping by last name,
not first name, but you can see we've got three, three, two, two.
And that holds up with just how we hopefully remember the data.
Of course, we could always take a look and just do select.
How about title author L name from books?
And we see, you know, gay men.
One, two, three.
Lahiri One, two.
Eggers One, two.
Is there a third?
Three.
So in this case, we can verify our work by counting the the old way, just counting, you know, using
our fingers.
But we don't need to do that because we can have group by do it for us or group by along with count.
So let's try another example instead of grouping by the author's last name, why don't we try grouping
by the released year so we could figure out how many books were released in a given year?
So this table looks nasty because it's just too long.
With my font size, it all kind of gets collapsed.
But let's do a select released year from books.
We'll just start with that.
And you can see all of the years that we had different books released in and there's actually not that
much overlap.
We've already discussed this.
There's a couple of years that show up more than once, but not that many.
What I want to do now is select released year.
From books and group by.
Released here.
And this is not going to do a whole lot for us.
It basically will just show us the distinct years.
But that's because we're not taking advantage of those groups that have been formed in memory.
So in memory, there's a group that has I think there's two books in 2003, so there's two rows and
then there's two rows for 2001.
But we only get one printed out because there's a group here and a group there and so on.
So I can use count once again count Star and this is going to count the number of rows in each group.
And we see 2003 appears twice, 2001 appears three times.
And well we've got Noel Darren Nolan there appears twice for the year 2017 once and then we could order
them, we could limit, we could do whatever we want.
But what I care about right now is this mechanic of grouping into smaller groups, these little mini
tables.
You can think of them kind of as many tables that have one or more rows, depending on if there's multiple
years of 2000, three, multiple 2000 ones, those are grouped together.
And then we're asking for the count.
How many of those rows are present in each group?
Okay, One last thing that's important to talk about is we have all of these rows, right?
Select star from books, a rather all of these columns, book ID title released years quantity.
I can't do something like select star from books group by.
And then how about we grouped by author last name again?
I'm going to run into trouble here because my SQL is getting complain and say, You you asked me to
make all these groups based off of the author last name, which I did.
Again, pretend it looks like this, these groups based off of the last name.
But then you said Select star and star means every column.
So how do I select a title?
How do I select a year or release year?
How do I select an ID?
Because I have more than one of each of those.
It doesn't work, so it gives me an error.
It says, Well, you could change the SQL mode, and if you do that, you can kind of make it work.
It will show you just one result or one match for each column, but I don't want to do that.
Generally what we do is we make our groups and then we use some aggregate function like count.
That's the only one we know so far.
But we're going to learn men and Macs and average and some others.
So we use that to boil down multiple rows in a group into a single value.
But also we can access any of the columns that we're grouping by.
So instead of Select Star, of course, I could select author last name because every row in that group
has the same author last name.
If we look at our visualization of the groups, we can select last name and print that out.
Because Lahiri and Lahiri, it's the same.
There's no ambiguity, but I couldn't ask for title, as we've already discussed.
Author, Last Name, comma Title.
Which title?
There's a group.
There's more than one title there.
What do you want me to group or what do you want me to to display?
That's me personifying SQL.
So that's important to understand.
Generally the formula is will group by some column, and then we might use that column in our select,
but then we almost always are going to have some aggregate function going on like account min, max,
average and sum.
So I think that's a good note to end on.
Let's move on and start learning some of those other things we can do those other built in aggregation
functions.