‫Welcome back everyone to part one of our lecture series of group by.
‫Group VI allows us to aggregate columns per some category.
‫Let's first explore this idea for a simple example, and then we'll go over the SQL syntax.
‫Let's imagine we have the following table.
‫We have some sort of category column here.
‫We have A's, B's and C's, and then some data value associated with them, such as ten, five, two,
‫etc. We could imagine that maybe the category here stands for some sort of company, and the data value
‫is how much we sold to that company.
‫So we had one deal of $10 for company a later on we had a $5 deal for company a then a $2 deal for Company
‫B and so on.
‫So when we're actually performing a Group B, there's a couple of key things to consider.
‫The first is this category column.
‫We need to choose a categorical column to perform the group buy on.
‫That is to say categorical columns are non continuous, or at least we're treating them as non continuous
‫for the sake of the group buy.
‫Now something to keep in mind is these can actually still be numerical, such as cabin class categories
‫on a ship, class 1 to 3 and so on.
‫And you should also keep in mind that they actually may appear continuous, but you could treat them
‫as a category.
‫For example, if you recall to our DVD rental database, we have different rental rates available,
‫such as $0.99, one, 99 to 99, etc. We could treat each of those rental rates as its own separate
‫class or own separate category.
‫So just because something is being treated as categorical doesn't necessarily mean it's non numeric.
‫And we're going to see different examples of that in the next lecture when we actually program out in
‫SQL, some group buy statements, but let's first go through the idea of what a group buy actually does
‫and how it relates to aggregate function calls.
‫So the way a group buy operates is the following We have already chosen our categorical column.
‫Then what we're going to do is essentially split this table up on a per category basis.
‫So we have everything that went with category A, then B, then C, and then we have the corresponding
‫data values.
‫Recall that an aggregate function takes in multiple values and reduces them back down to one single
‫value, such as the sum function can take in all these values per category or grouped by category and
‫then return back their sum.
‫So the total sum of sales to category A was 15 six to B and An 18 to C, and we could do this with any
‫of the aggregate functions that we just learned about.
‫For example, we could take the average data value per category 7.5, three and nine.
‫We can even do something like a count per category.
‫In this case, there was two rows per category, so they're all the same two, two and two.
‫So let's actually explore the general syntax.
‫So the most basic group by call looks like this.
‫We select some sort of category column or something we've deemed to be categorical, and then we choose
‫to perform some aggregate function on the data column.
‫So AG here is just some placeholder for some aggregate function.
‫On some data column, we select it from the table and then immediately after that we call group by that
‫category column.
‫So the group by clause must appear right after a from statement or right after a wear statement.
‫So if you want before you do the group buy, you can filter things out using a wear statement.
‫So here we see just grouping everything together by the category.
‫But if we wanted to before we actually execute that group buy, we could have done a wear statement
‫essentially saying something like where category column is not equal to a essentially ignoring all the
‫categories before we went ahead and did the group buy.
‫So the thing keep in mind is the group buy either has to come directly after a from statement or directly
‫after a wear statement.
‫Now, one of the most important things to realize when learning about group buy is the following in
‫the actual select statement, columns must either have an aggregate function, call on them, or be
‫themselves in the group buy call.
‫So what does that actually mean?
‫This is where beginners often get tripped up in learning about group buy, so pay close attention to
‫this one.
‫So if we're using a group buy statement, then if we're selecting that particular column just by itself,
‫it must be included in the group buy statement.
‫So notice here I've decided to select the category column.
‫Then that particular column must also appear in the group buy statement.
‫The only exception to this is if I'm calling some sort of aggregate function on the column itself.
‫So in this case, because I'm aggregating something on the data column, it does not need to be included
‫in the group buy statement.
‫So let's imagine a different example.
‫We can have something like select company division and then some of sales.
‫So we can imagine we had three columns in this table.
‫One were different companies and then the divisions within that company and then different sales per
‫division per company.
‫So what we're doing here is, again, notice that if I'm selecting a column such as company and division,
‫then they must also appear in the group by statement.
‫So that means we can group buy multiple columns and we essentially just decide the order to group by
‫in a very similar fashion that we decided the ordering of different columns when we learned about the
‫order by statement.
‫So again, here I'm selecting column and division.
‫Since they don't have an aggregate function, they must appear in the group by clause.
‫Notice.
‫Again, the sales doesn't appear in the group buy statement, which means it must have an aggregate
‫function associated with it.
‫So essentially what this is returning is the total sum of sales per division, per company.
‫And keep in mind that the wear statements should not refer to the aggregation result.
‫So you shouldn't be trying to do any filtering using a wear statement on some of sales in this case.
‫Later on, we are going to learn how to do that using the having statement to filter on those results.
‫But for right now, if you are using a wear statement in conjunction with a group by that where statement
‫shouldn't make a reference to what is being aggregated, such as some of sales, that is what the having
‫statement is for that we're going to learn about later.
‫So notice what we're doing here is we're saying select company division, some of sales from finance
‫table where a division is in marketing and transport, essentially filtering out the divisions before
‫we actually perform the group buy on the company and division.
‫And again notice here company and division appear in the group by sales does not because it has the
‫aggregate function associated with it.
‫Here's another example that if you want to sort the results based on the aggregate, you have to make
‫sure to reference the entire function.
‫So in a simpler example here, let's say we wanted to sum to sales, total sales per company, then
‫we could select company some of those sales from the table, we would group by company.
‫And then the other thing to notice here is if I actually wanted to order by the sum of sales, I don't
‫just say order by sales, I have to say order by the sum of sales.
‫So notice that whatever I'm ordering by that total statement should exist somewhere in that select clause.
‫And then you could add something like limit five, which would essentially show you kind of the top
‫five sales or bottom five sales depending if you did ascending or descending order per company.
‫All right.
‫So I know we just went over a lot of this and it's best learned through practice.
‫So I'll see you in part two in the next lecture where we actually practice on our own DVD rental database.
‫I'll see you there.