One more quick thing we need to cover with Group B is grouping by multiple columns.
So with our authors or books, data table data set, we know that there's authors that have the same
last name but not the same first name.
I think it's just two authors.
Let's do author.
F name.
Author L name from books.
And then let's order by author.
L Name by the last name.
So here we can see Carver, Eggers, Foster Wallace.
And then we get to Harris.
We have Harris, Dann and Harris Freedom.
And when I do a group by by the last name, which is what I did earlier, and let's say I counted up
the number of books that each wrote, which we've done already.
Select Author L Name comma Count Star from Books Group by the last name.
I did this, and it was technically true that Harris has two books, but this doesn't really tell the
picture that there's two authors there.
We just know it's one last name.
Harris So I probably want to write a query that actually groups by the last name and the first name,
what might be better is to group by the full name, which doesn't exist, and maybe we should store
it that way.
But we didn't.
Or I didn't.
So instead we can add on a second part to the group by after author l name will do author F name for
first name.
And if I do it that way.
Well, now we just have Harris showing up twice.
Why is Harris there twice?
Because there's two different groups.
One is based off of Harris and then Frieda and the other is Harris.
And then Dan, I think it's Dan.
I'm only displaying author L name, but I could add in author F name if I wanted to.
Let's just put this over here so we have it.
I'll reformat it slightly and say that we want the first name, author F name, and then the last name
and then the count for each one of those groups.
Now, most of those groups are going to be the same first name and last name, so we won't notice a
difference.
Like there's only one Jhumpa Lahiri.
But in our case, there are two different Harris's Dan and Freedom.
So there we go.
We get our counts for each one of those groups.
Jhumpa Lahiri two.
Neil Gaiman three.
Dave Eggers three.
But once we come down here, we get a different result because there's two different Harris's.
So that's important to know that you can do it that way.
We honestly could even group by just one thing if we concatenated author first name and last name to
be a full name like this select Can cat.
We've seen this before F name and then maybe a space and then author L name from books.
Let's make sure that that works first.
There's no grouping whatsoever.
Here's the full names.
Let's rename that give it an alias as author from books.
And then what I can do is group by that exact property or that exact thing I just created here called
author grouped by authors.
It's not a column that exists in the table, but I can still group by it.
And then instead of just selecting the concatenation, I'm also going to select after this whole field
here called Author, I'm going to select Count Star.
So how many rows exist in each one of these groups that are created by Author?
So the order of all this seems weird, but it will work.
If I didn't mess up any any syntax I pasted in.
And there we go.
We see Jhumpa Lahiri two, Neil Gaiman three.
So we get the same results, the same counts that we did when we grouped by the first name or the last
name and then first name separately.
But the mechanic of this is slightly different.
We're only grouping by one property or one column, but that value happens to be the result of concatenating
first name with a space and then last name.
And then we count up how many occurrences or how many rows have that as their author.
And for a lot of them it's just one.
But for some of them, like Dave Eggers, that full name occurs three times when it's formed for each
one of those rows.
So that's grouping by multiple values, even though at the end of the day, this example is just one
value.
This one here we're grouping by two different columns and you can keep going.
So it's not totally uncommon to have multiple pieces to a group by.