Okay.
So next up, we're talking about something called group B and Group B is going to be essential to everything
we do in the rest of this section.
And I'll warn you right now, take a deep breath that it's a little bit weird.
It's different than the things that we've seen so far.
And in fact, for me personally, it's a challenging one to teach because like like I just said, it
is different.
It's not just a single little function that we can throw in there, like Kin, Cat or uPAR, where we
get a nice immediate impact, where we can see that was uppercase or that was concatenated or that's
account.
Rather, we have to use it alongside some other functions.
So it's going to take a little bit to explain.
So hang in there and just know that we're going to get there.
There is a payoff and it is very useful.
It's kind of the core of aggregating our data.
It's how we can do things eventually, like average or some or find the minimum or the maximum and so
on.
So here we go.
This is a little blurb that I wrote about grouped by group, by summarizes or aggregates identical data
into single rows.
So it's actually pretty self-explanatory.
The name group by we can say things like take all of our books and group them by the year they were
released or grouped them by the author last name or take all of our TS or actually let's say take all
of the movies in our database and group them by genre, and that on its own is not that useful.
But then we can say things like group them by genre and tell me how many each genre has or group are
teas by variety, like green tea, white tea, black tea and so on, group arts by variety.
And find the average sales price for each one.
For each variety that is, or add together all the sales for each variety and figure out which tea type
is selling best.
So we'll get there in time.
But for now, let's start with a little example.
Again, here's a reminder.
Stay with me, please.
Let's say we're working with a very small subset of our book data.
So I just grabbed five books.
So when we do select title and author L name from books, right now we'll get 19 in our actual database.
But this is a fictional database where we get five books.
We've got The Namesake by Jhumpa Lahiri.
Norse Mythology by Neil Gaiman.
American Gods Parnell Gaiman.
Interpreter of Maladies by Jhumpa Lahiri.
And A Hologram for the King by Dave Eggers.
All right, so we've get these five books out when we just select just like that, we don't do anything
fancy.
We're just getting title and author last name.
So then we introduce Group Buy and we'll talk about the syntax a little bit.
But all that we've done, this is the exact same line as before.
Select Title An Author, Last name from books and we've added on at the end group by author name.
And I've just done it on the second line just to make it so that this is all easier to read.
Again, it doesn't matter.
So group by author last name and what we get looks like this, which is bizarre.
It's probably not what you would expect.
It's not what to me makes sense on a visual level.
At least it does make sense once you learn what's happening.
But on a visual level, it looks like basically it's only just giving us the unique authors like Lahiri,
Gaiman and Eggers, and then just picking their first book title.
Right?
So we're getting The Namesake for Lahiri, Norse mythology for Gaiman, and a hologram for the King
for Eggers.
But then we're missing these two books.
And that's that is what's happening.
That's what we're seeing here, which isn't really useful.
Why would you want that?
So what's actually happening is that even though it's just prints out The Namesake by Lahiri, it's
basically grouping based off of last name.
And so it creates one big, let's call it a super row with all of Lahiri's information or all of the
books written by Lahiri.
So that's the namesake and interpreter of maladies, and it treats that as one row.
And it does the same thing for Neil Gaiman.
And then the same thing for Eggers.
And there's only one Dave Eggers book in our data set.
So even though this is all that we see behind the scenes, they are grouped together in these super
mega whatever we want to call them rows.
And that's important because the data is now grouped and we can do things like this.
Count how many books each author has written.
So our query would look something like this.
Select author LastName Comma.
We don't have to have this here either, but it's like the author last name comma count star from books
grouped by author last name.
So it's going to group them like we've already seen.
And then when we do a count star, it's not going to count every row in our entire table, but it's
counting is for each of these mega rows.
How many sub rows are in there?
So basically how many things are under the theory?
And in this case, there's two.
How many things, how many books are grouped under?
Gaiman Two.
And how many are under Eggers?
One.
So then what?
We get spit out when we're actually selecting the author last name and the count is this Lahiri to Gaiman
to Eggers one.
So we're only getting started here with Group B.
I'm going to now dive into Cloud nine and do some more.
But just know that we will be using Group B to do things like average.
So once we have, let's say, grouped by authors, we could average the page count, like I said, or
the average year that they wrote a book.
Or we could find the first year they wrote a book using Min, which we'll see in just a moment.
Or we could sum the total page count, for instance, for every author.
So group is very important and versatile.
For now, we're only using it in conjunction with Count.
So now let's dive into Cloud nine and do just that.
So let's start with something simple like select title comma, author l name.
Let's do actually first name and author.
Last name from books.
So no count or anything like that yet.
So we have a much bigger data set, right?
Let's do a group by now.
Group.
Bye.
And we'll do author last name.
And we're going to just do the exact same thing.
I want to know how many books each author has written and we will run into problems.
And just telling you now with this.
HARRIS Right.
Because we have two different Harris's free two.
Harris and Dan Harris.
But when we grouped by last name, it just treats them as one.
So it will say Harris has two books, which technically is true.
The last name Harris has two books.
Okay, so if we do this and just hit enter, it's not useful.
You.
I don't know why you would ever do that.
Honestly, it just is giving you sort of a preview of the the mega rows, if you will.
Basically, it's just taking the first book by Carver, the first book by Harris.
But we don't see how they're actually grouped.
So it's not that useful.
It's not useful at all, honestly.
So what we want to do is, rather than just title, author, first name, author, last name, let's
do Count Star.
And remember, Count Star in this case is referring to the groups rows.
So it's going to go to each of those group rows and count how many rows have been grouped together.
Oh, and this is kind of a mess to look at, so let's actually get rid of title.
And just to author first name and last name, now we can see.
So Raymond Carver has to Don DeLillo has one.
Dave Eggers has three.
David Foster Wallace has two.
And then, as I mentioned, we have this problem with Harris because it says Dan Harris has two, but
that's not true.
Dan Harris has one book.
Frieda Harris has one book.
So to get around that, of course, we can do the same thing where rather than just doing group by author
ll name hopefully you've seen it.
This is something that's kind of standardized across MySQL.
We can do author f name as well so it will group where both of these are unique.
So then we do that and everything else is the same.
Raymond Carver has two, Dave Eggers has three, Foster Wallace has two.
But we go down here.
We now have Dan Harris with one and Frieda Harris with one.
All right.
So just to show you another thing that we could do, we could also group buy released year.
So we could regroup by title, of course, but we don't have any books that have the same title.
But we do have a couple of books that have the same year.
So let's say we want to get a nice printout that shows us in 2017, one book was released.
In 2016, two books or however many books were released.
So we can do that pretty easily with just a select and let's do released year comma count star.
So if we just do select release year from books, you can see where it's an example.
I think 2003, we've got at least two, 2000 threes and I know there's a couple more.
I hope there's a couple more.
So let's say we wanted to condense them all together and then put a count next to it as well.
We would do that just like this select release year from books.
We would group by release year and rather than only selecting the release year, we also want to select
Count Star.
Let's hit enter.
Now you can see we've got 1945 one book, 2001.
There were actually three releases, 2003.
There were two releases.
So you can see that it's working.
And if you want to get really fancy, go ahead, move on to the next video if you'd like.
But I'm just going to show if we want to get fancy and print it out nicely, we can combine it with
concatenation.
We could do something like this.
So rather than just selecting the count.
We could do.
Let's see a select kitten cat and we're going to concatenate the release here comma.
So let's do actually let's add a string before.
So I'd like it to say like in 2010, two books released in one book, I guess in 2001, three books
released.
I actually want that string so we can do in the release year.
Comma.
The Count.
Comma.
Books released.
And technically, just to make sure let's add parentheses around the SE because there's in a lot of
cases one book release.
So I don't want to say one books anyways, so let's make sure we have our correct parentheses here.
Select the concatenation of in the released year.
We probably want to space there, don't we?
This is where it gets kind of annoying that we're not using a separate file.
So in 2001, let's say space three space books released.
And then lastly to clean this whole thing up, let's add an as and we'll just call this as let's just
say year or something silly.
We hit enter and we get this nice little table in 1945, one book released in 2003.
Two books released in 2001 three books released.
Okay, so that's it for now, at least with Group By.
We're going to be working with it in every video from here on out in this section.
Like I said, we're going to move on to things like Min and Max average, some fun things, hopefully
sort of fun.
Congratulations if you made it through this video.
Like I said, it's a little bit weird.
It's different than what we've done before.
Hopefully it wasn't too boring.