Hey, welcome back everyone to part 17 of topic number three in our database class. In this video, I'm going to show you how you can use the SQL group by and having statements. Now, in the structured query language, there is a mechanism built into the language that allows us to generate subtodels or values that apply at a categorical or group level. And this is the group by clause that is built into the SQL select statement. Now, what group by does is rather than producing something like an overall sum or an overall average or an overall minimum or maximum or standard deviation, the group by allows us to compute subtodels by groups or by categories. So, for example, if I wanted to know, say, how many employees work in each department? Well, I could ask the database to answer that question with just one single select statement similar to what we see here, and it introduces this new concept of group by. So, let's just take a look here and see if we can start to build an intuitive understanding of what is happening. Now, you'll note that we are selecting two pieces of information. So, these two pieces of information will appear in our output. The first is the department ID. Remember my goal here is to have the database tell me how many employees work in each department. So, to identify the different departments here, we will have the department ID. That is the first piece of information that we are including in this select statement. The next piece of information that we want is the count. That is how many rows there are. And you can see we're using an alias here to refer to that count as number of employees. So, our output then will consist of the department ID, and then the number of employees in that department. Of course, we still have to use the from. We need to tell the database where to look to get this information. So, we're saying, hey, database, you're going to have to look at the employee table. And then here is this new clause, the group by clause. So, you can see we're telling it that we want the results to be grouped together by department ID. So, what this will do is it will say, okay, I understand that what you want is the number of employees per department. Rather than the total number of employees. So, we're using this column name that appeared up here in the select statement to perform the grouping. Now, just as a very quick piece of practical advice, whenever you are doing something like this, that is, whenever you're using a group by, whatever columns appear here that is the number of people. So, here, here that appear up here in the select statement that are not a part of an aggregate function. So, our aggregate function here is count, but it could just as easily be some or average or minimum or maximum, right. Whatever columns, just regular columns appear up here in the select statement must also appear in the group by. Okay. So, if you are selecting say, I don't know, three different columns up here department ID and I don't know, maybe we select some other things. All three of those columns would have to appear in the group by as well. So, if you do not follow this basic rule, you'll get an error message when you run your SQL commands. And it'll tell you something like, hey, you have a column that's not part of an aggregate function and that that should appear in the group by. So, let me show you some examples of this. All right, I'm going to switch over temporarily to our SQL server management studio. And as usual, we'll be working with our employees database and we'll use this to demonstrate some of the things that we'll be learning today. Now, if I take a look at the table, we can see that it has some attributes here, we've got my department ID in there and first name last name, higher date, etc. And I will use that table as the basis for doing our first demonstration of a group by. Okay, so let's open up a new query window and see what we can do here. So, we'll replicate what we just saw on the slide, which is to select the department ID as well as a count of those two star. So, it's a number of rows, right, as I'll say number of employees. And of course, we're going to direct to this query to the employee table. And we'll just kind of run this now so we can see the sort of error message that you might get if you do not set up the group by properly. So, in this case, I have a regular column department ID and then I have a aggregate function. These are the two pieces of information that I want to appear in the results and I'm not going to include the group by. I'll type it here for now, just so that we can see it in place, but what I'll do is I'll put comments in front of it. So, these two little dashes is a way of commenting out of line. This is going to be ignored and not run by the database when I submit this SQL statement. So, let's go ahead and run this, and we get this error message. And let's take a look at what it says here because you'll see this regularly if you make a little mistake with your group buys. So, this is telling me that department ID is invalid in the select list, meaning here. These are the columns or information that I'm selecting. So, it's saying that this column is invalid here because it is not contained in either an aggregate function. It's just by itself. It's not part of an aggregate function like account or average. Or the group by clause. So, this will not work in this case because it's not a part of the group by clause. So, if you get an error message that says something like that, you know immediately that what's going on here is you have just a regular column up here in the select list. That is, you know, not part of the group by or an aggregate function. So, if we let the group by in there, we're telling it to basically do this count and do the count by department ID. So, how many rows are there per department? Since we're looking at the employee table, it's basically how many employees are there per department. So, if I run this, you'll see now that we get this lovely list that includes these subtodels. So, we have departments 1, 2, 3, and 4. And we can see that they respectively have 5, 5, 1, and 1 employees. So, we're getting these subtodels, the number of employees per department. So, easy enough. We can, of course, use this for other things as well. Let's take a look here. See, we have this employee skill table. And it has employee ID, a skill ID, and a skill level. And we can use a group by here to calculate certain things that may be of interest to us. So, let's say, for example, that I want to know the average skill level per employee. I don't like to easily find that out just by making some changes here. This is going to be the employee skill table. And let's say that we want employee ID to be our grouping column. So, we want the results grouped by employee ID. And then what we want is the average skill level. And I'll call this, I don't know, average skill level. Very cleverly named. And if we run this now, we can see that we can easily get the average skill level for all of our employees. So, employee number one, whatever skills that employee has, the average is 7.5. Right for employee number six, 5.5, and so on. So, we get these subtotals. In this case, by employee, we could similarly say, what is the average skill level per skill? Right. In that case, I might do like a skill ID here. And we'll group the results by skill ID. And in this case, we'll see all of our unique skills. And the average level across all of our employees for them. So, you can see that skill 103 has an average skill level of 10 across all the employees who have that skill. Whereas, I don't know, a skill number 107 has an average skill level of about eight and two thirds across all employees who have skill number 107. So, these subtotals can be very, very useful in allowing us to write SQL statements. And you can imagine things where you say, okay, I don't know, what are my total sales for each store in California. Right. You can use this structure where you have a select and a group buy as a basis for answering those types of questions. So, lots and lots and lots of different business oriented questions can be answered. Writing SQL select statements that include a group buy because we all want to know these things. What are my total sales per salesperson this month? So, what you really want there is like the sum of sales broken down by salesperson so that you can see which salespeople are the most released successful during the past month. So, any of these things where you're looking at sub totals or you want things to be grouped together or categorized group buy is an excellent tool to use for that purpose. All right, let's resume. Let's see some more examples here as we make progress. So, another interesting thing that we could do if we're going to use a group buy and this is optional. But we can include a having clause. Right. This is only used in conjunction with a group buy and is optional. You cannot use this unless you also have this, but you can use this without having this. So, having is an optional extension to group buy. And what having does is it allows us to filter results at a categorical level. So, previously we were using the where clause to filter results out one row at a time. So, if something like meets a criterion specified in a where clause, then that means that row can be included. And having is exactly the same idea except it's filtering here on these categorical outcomes. Okay. So, let's take a look at what we have in this example. So, you can see here we are selecting the salesperson's ID number and their last name. These are just ordinary normal columns here in our select list. And we'll see that both of those columns appear in the group buy separated by a comma. So, this means we're going to group the results first by salesperson ID and then by last name. So, because we have these ordinary columns listed here, they must must must appear here in the group buy. Otherwise, we'll get that error that we saw a moment to go. Okay. And then we have our aggregate function, which in this case is a sum. And what we're doing here is we're getting a sum of the sale amount. And we're going to call that total sales. Okay. So, what we're getting then is a total sales for each salesperson. Of course, we're looking at a sales table for this. We tell it that we want to group things by the salesperson's ID and last name. And then we see the having. And this is our filter that's being applied here to our group buy. So, the three pieces of information that would appear in the output here will be the salesperson ID. The last name. And then there's some of sales. Okay. Now, with that in mind, what we're doing is we are restricting the results to those salespeople that have sold a total of at least $10,000. So, in this case, if we just say dollars, any salesperson that has not sold at least $10,000 worth of stuff will not be included in the results. So, in this case, we're essentially saying we want to see the total sales for each of our salespeople, but only if they've sold at least $10,000 worth of stuff. So, these are our highest performing salespeople, for example. So, we're using the having here to filter out rows based on our aggregate function, which in this case was a sum. So, it could easily be an average, right? Maybe you only want to see people. You do something like, I don't know, show me the employees in my accounting department that have an above average salary. So, in that case, we would want to filter out those whose salary was less than the average. So, having can be very useful for this purpose. Basically, allows us to filter things out on a categorical level. So, let's, before we get into sub queries, let's just see some sort of example of this. Let me switch back over here. All right. So, in this case, we were doing, I know what I'll do employees instead of skills. We'll do employee ID. And we're just getting the average skill level by employee. So, if you remember, this looks a little something like this, right? Now, let's say that, I don't know, we only want to see those whose average skill level is, I don't know, greater than eight, right? So, in that case, we could filter out the ones that have a comparatively low average skill level by using a having. And in this case, what we would do is we would say we want your average skill level to be greater than eight. And if we go ahead and run this, you'll see that the only results we get now are those where the average skill level is greater than eight. So, we're filtering out our comparatively lower skilled employees by taking this sort of approach. All right. Say we wanted only those employees who had an employee ID of, I don't know, greater than, greater than or equal to 10. Okay. So, I could put a filter in here to filter out first, filter out any employees whose employee IDs are less than 10. I'm sorry, I want the greater than or equal to 10 to be included, right? So, this will perform an initial filter, right? It will limit the number of rows then that are used to compute the averages. So, we'll do that. Just include, put a where in there and you can see now we're getting a much more specific question. We're asking the database here, hey, database, give me the average skill level for all of my employees where the employee ID is at least 10. And the average skill level is greater than eight. So, that's a pretty specific question. But you can see we can just include the where there. And the note that the pattern is select from where and then group by and then having. Okay.