00:00Hello everybody. In this lesson, we're going to be taking a look at group by and order by in MySQL.
00:05Now when you use the group by clause in MySQL, it's going to group together rows that have the
00:10same values in the specified column or columns that you're actually grouping on. Once you group
00:15those rows together, you can run something called an aggregate function on those rows. Let's see
00:20how this actually works. Let's go ahead and copy this right here. We'll bring that down and let me
00:27pick up one. Let's go ahead and write gender right here. Now we want to group on this gender column,
00:35and we're going to say group by gender. Let's go ahead and run this. We'll see what we get.
00:43And so we have male and female. Now we could get the exact same output by saying select distinct
00:51gender from this table. What is group by doing that the gender actually isn't doing? Well,
00:56it's actually rolling up all of these values into these rows. So later when we run aggregate functions
01:03like average, min, max, we'll do it based off of these rows. And all those rows are rolled up into
01:10these two rows. And we'll see that in a little bit. Now, what if I was to come up here
01:14and in this
01:15demographics, we have a first underscore name. What would happen if I'm selecting the first name,
01:20but I'm grouping by the gender? Let's go ahead and run this. If we come right down here, we pull
01:27this
01:27up, you can see that the select list is not in group by clause and contains non-aggregated columns.
01:33What this means is that when you are selecting a column, if it's not an aggregated column, like
01:38say average of something, if we're not using the aggregate functions in the select statement, it has to be
01:44in the group by. These have to match. So this gender has to match this group by if we're not
01:50performing an aggregate function on it. Let's go ahead and run this. And now it works properly.
01:57Now let's go back up. Let's run this query because I want to select everything again.
02:01Well, let's say we wanted to take a look at the average ages for gender. So what we're going to
02:07do
02:07is we're selecting gender. We're also grouping by gender. But what we're going to do is add a comma
02:12and we'll say the average. That's A-V-G. That stands for average. And then we're going to put
02:17in here age. So now this right here is an aggregate function. This does not need to go in the
02:23group by.
02:24We're just grouping on the gender and then we're performing this aggregate function or kind of a
02:29calculation based off of those grouped rows for gender. So let's go ahead and run this and take a
02:35look at the output. So what this is telling me is that for the males, all of the male rows
02:40that were
02:41grouped, the average age is 41.3. And for female, the average age is 38.5. So super quickly, you
02:51can
02:51tell that the average age of females is lower than the average age of males. Now we'll take a look
02:57at
02:57aggregate functions more in just a little bit. Let's actually go to a different table. Let's come
03:03right down here. We're going to go to the salary table and just select everything for now. Let's go
03:11ahead and run this. Now what we're going to actually be grouping on is this occupation right here. Now
03:18there's a lot of unique values. It's not as distinct as the gender, which only had two values. You'll notice
03:24we do have a few that are the same. We have ones like office manager. So when we come up
03:28here,
03:28I'm going to say occupation. And of course we need to group by the occupation as well. Now let's run
03:35this. You'll notice that office manager only has one row. Let's say we also want to group on the
03:42salary. Let's say salary. Now we can group on multiple. So we're going to say salary like this.
03:50So we're grouping on the occupation as well as the salary. Now let's run this. You'll notice that we have
03:57two rows for office manager. Now this is because this salary and this salary for those two employees
04:03are different. We have 50,000 and 60,000 for this. I just wanted to demonstrate that if these had
04:09both
04:09been 50,000, there would only be one row office manager, 50,000, but because this is a unique value
04:16different than 50,000, they have their own individual rows, which we would then perform our aggregate
04:22calculations on. Let's go and get rid of that because we will not be using that anymore. I just wanted
04:26to
04:26demonstrate it really quickly. So before we were looking at gender and average age, and we're also
04:31grouping on the gender. We can perform other aggregate functions as well. Let's take a look at some of
04:37those. We could look at the max age as well. The max is going to show us the highest value
04:44within each of
04:45those groupings. So we have a male and female. The max age for those for the male is 61 and
04:52the highest age
04:56exactly what you can say. Men are the exact opposite thing. We can say the minimum age. So this is
05:01going to be the lowest for both the male and the female. Go ahead and run this. Now we have
05:06female and male, and the minimum age is 29 and 34. And there is one last one that I want
05:12to
05:12show you, which is count. We're going to do count. Now count is going to count the actual rows within
05:19this age
05:20column. So if we run this, you'll see that we have four females for count, and we have seven males.
05:27It's just
05:28telling us a count of how many values is in this column when we're actually grouping on the gender.
05:34So that's how we can use group by to actually roll up and group all of these similar values within
05:41a
05:41column or columns and perform our aggregate functions on them. Now let's come down here.
05:46And what we're going to take a look at is order by. So I'm going to say order by. Now
05:51let's actually pull
05:53in this demographics table right here. We're just going to say select everything
06:02and run this really quickly after we add a semicolon. So order by. Order by is going to
06:09actually sort the result set in either ascending or descending order. Let's take a look at how this
06:15works. At the very end, we could say order by, and we could order by the first underscore name.
06:24So we're going to take this column. We're going to order all of our rows based off of this one
06:29column.
06:29Let's go ahead and run this. So it's going to do it based off ascending order, which means
06:34smallest to largest. Now this is a text column or a character column. So we do it A to Z.
06:40So Andy
06:41and April all the way down to Tom. Now by default, this is in ASC order, ascending order. And if
06:49we
06:49run this, it's going to be the exact same output, but we can change this to do it the opposite,
06:54highest to lowest or Z to A by doing descending. So now if we run this, you'll see that goes
07:01Tom
07:01all the way down to Andy. Now let's take a look at ordering on something like gender and age,
07:08because we can do both at the same time. So let's order by the gender first. Let's go ahead and
07:13run
07:13this. And you'll see that all the females are grouped together. And then all the males are
07:19grouped together because that's just the order in which it is. But we can do an additional column.
07:24We can also do it based off of the age. Let's go ahead and run this. So now within the
07:30female,
07:31since that came first in our order by, we're ordering by the gender. And then we're also ordering
07:37by the age after we've ordered by the gender. So now it's 29 all the way up to 46, and
07:44then 34 for
07:44males all the way up to 61. Now we can change this just for the age. Let's say we want
07:50to do age
07:51descending. So gender will stay the same in ascending order, but now age will be in descending order.
07:57Let's go ahead and run this. Now female and male stayed the same, but now it starts at the highest
08:03down to the lowest. Now this is something that I would absolutely do in real life, except sometimes
08:09you can make mistakes and sometimes you do the wrong column first. Let's do age and then we'll do
08:14gender. Now, if we run this, the gender is not going to be used at all. And this is because
08:22there
08:22are no unique values that are going to be on the same row. So notice all of these values are
08:28completely
08:28unique. So the gender never is actually used to order anything on because if there were things
08:34like 34, 34, 34, 34, these would be ordered based off of the gender. But since there's no unique fields,
08:42this is really pretty useless. That's why the order of the order by or the columns that you place in
08:47the
08:48order by are actually quite important. Now the last thing that I want to show you, and I'll just go
08:52back
08:52to gender and age, is that you don't actually have to use the column names. We can use the column
09:00positions. Now I will preface this by saying I don't recommend doing this, but I sometimes do it in
09:06shorthand for just a quick query if I know the column position and I don't want to write out the
09:11whole name. Sometimes I do it, although it's not best practice. But let's take a look at it. So gender
09:17is the
09:18one, two, three, four, fifth column. I'm going to replace this with five, and age is the one, two,
09:24three, four column. So these are the positions of the fields, but not the names of them. If we run
09:31it,
09:31we're going to get the exact same output because these represent these columns appropriately. But
09:38again, I just don't recommend it. It's kind of a slippery slope that I've fallen down myself
09:44many times. And when you get to more advanced SQL and you're creating things like store procedures
09:50and triggers and all these things, this can actually cause a lot of issues. If you were to
09:54add any columns or remove any columns, then you'd be ordering by the wrong column because let's say
10:01this last name got removed. We didn't want it for some reason. Then the gender is one, two, three,
10:07four. Now we're ordering on the wrong column, and that would be a big mistake. So just by best
10:14practice, it is better to do gender, age, but I just wanted to show you that in case you want
10:21to be
10:21like me and kind of go down the wrong path. So that is everything we're going to take a look
10:27at with
10:27group by and order by. In the next lesson, we're going to be taking a look at having versus where.
10:33if you're going to be.
10:44You're going to be taking a look at the video next week.
