OK so next up we're talking about something called GROUP BY AND group 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 concat or upper where we get
a nice immediate impact where we can see OK that was upper case 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 at 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 group by group by summarizes or aggregates identical
data into single rows.
So it's actually pretty self-explanatory the named group by we can say things like take all of our books
and group them by the year they were released or group them by the author's last name or take all of
our t's or actually say take all 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 Arty's by a variety like green tea white tea black tea and so on group are teased
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 the 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 the 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 the Henry Norse mythology by Neil Gaiman's American Gods final game an interpreter
of maladies the Harry and a hologram for the King by Dave Eggers.
All right so we get these five books out when we just select just like that we don't do anything fancy.
We're just getting title An author last name.
So then we introduce group.
And we'll talk about the syntax and a little bit but all that we've done this is the exact same lines
before.
So like title An author last name from books and we've added on at the end.
Group by author Al-Naimi.
And I've just done it on the second line just to make it so that this is easier to read.
Again it doesn't matter so group by author last name and what we get.
Books 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 when you learn what's happening but on a visual level it looks like basically it's
only just giving us the unique authors like a hero game in and Eggers and then just picking their first
book title right to we're getting the namesake for the theory Norse mythology for game and the hologram
for the king for Aggers.
But then we're missing these two books and that's that is what's happening.
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 just prints out the namesake by Lahiri it's basically
a grouping based off of last name.
And so it creates one big let's call it a super hero with all of his 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 Aggers and there's only one Dave Eggers book in our dataset.
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 rose 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 last name comma.
We don't have to have this here either but it's like the author's last name.
Comma count star from books.
Groups by author last name.
So it's going to group them like we've already seen.
And then when we do account 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 here and in this case there's two.
How many things how many books are grouped under Geymann two.
And how many are under Eggers 1.
So then what we get spit out when we're actually selecting the author's last name and the count is this
Lahiri to Geymann to Eggers.
So we're only getting started here with group by.
I'm going to now dive into cloud 9 and do some more.
But just know that we will be using Group By to do things like average.
So once we have that say group by authors we could average the page count.
Like I said or the average year that they wrote a book or we could find that the first year they wrote
a book using men which you will see in just a moment or you 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 9 and do just that.
So let's start with something simple like select title comma.
Author L name you actually first name and author last name from books so no count or anything like that
yet.
So we have a much bigger dataset right.
Let's do a group by now.
Group By.
And will 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 Frieda 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.
OK so if we do this and just hit enter it's not useful.
You pretty I don't know why you would ever do that.
Honestly it just is giving you sort of a preview of 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 group rose.
So it's going to go to each of those group froze and count how many rows have been groups 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 two.
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 too but that's
not true.
Dan Harris has one book Frida 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
name hopefully you've seen that this is something that's kind of standardized across my ass.
Q All 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 to 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 By released here 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 wanted to get a nice printout that shows us in 2017.
One book was released in 2000 16 two books.
Or how many books are released so we can do that pretty easily with just a select.
And let's do the least year comma count star.
So if we just do select release year from books you can see that as an example I think 2003 we've got
at least two 2003's and I know there's a couple more I hope there's a couple more.
So let's say we wanted to condense them altogether and then put a count next to it as well.
We would do that just like this.
So release your from books.
We would group by released year and rather than only selecting the release year we also want to select
count star hit enter.
Now you can see 1945 1 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 you how to get fancy and printed out nicely if we can combine it with the
concatenation.
We could do something like this.
So rather than just selecting the count we could do.
Let's see a select can cat and we're going to concatenate the release here comma so as to actually 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 release actually want that string so we can do in the release here comma
the count
comma
books released and technically just to make sure the parentheses are on the SB kissers.
And a lot of cases one book really so I don't want to say one books anyway.
So let's make sure we have the correct premise here.
Select the concatenation of in the release 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 say space three space books are released.
And then lastly to clean this whole thing up let's add an as.
And we'll just call this as you 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.
OK.
So that's it for now.
At least with a 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.