In this section we're going to talk about aggregate functions, which are just summary functions that
reduce groups of values down to one value.
More specifically, they produce generalizations about a group of values like count, average, sum,
min, and max.
So for example, I could use max to search for the employee who sold the most products, or I could
use average to search for the average salary for each employee in a given department.
So let's go over to SSMs and see how they work.
So I'm going to open a new query window here.
And I'm just going to do some examples.
I'll give you these as a resource at the end of the lecture.
And these are going to be aggregate functions.
And we are going to start with the first one which is the count aggregate.
And basically it just returns the number of rows that result from a SQL query.
So let's first take a look at our customer table.
And you can see down here the editor tells you there's 107 customers.
And this is our data.
It's always nice to kind of see the data first before I start doing this.
And so if I wanted to know how many customers we have, I could just do select and then just do count.
And the argument here for count is really just going to be either star or it's going to be a field name,
but it's just going to be the number of results returned by the query.
So we would do select count from.
And then I will just drag out customers.
And if we execute that, then it's just going to be 107.
So instead of listing all the customers out, we can just say, hey, give me the count and it will
return that.
Now count acts kind of like a column name, so we can alias that.
So we could call this number customers.
And that's just going to give us a little bit better header name for our results.
Now let's say that we want to refine it further.
I'll put a comment up here.
How many customers do we have.
And then I'm just going to copy all of this.
And then the next one, let's say that we want to know a little bit more.
So we'll add who live in Florida or California.
So I can change this.
And I'll just put for our field name FL or CA.
And then I can just put a Where clause.
And looking at customers, the state they live in is state ID, so I'll do state ID and I'll do N.
And then we're just going to list the list of states.
And then I can just execute this.
And I get a quick number if we want to know the details of who actually lives there.
And we did this back several sections ago, we will just do select star from customers and we do get
that list.
And there are 16 rows.
So for now I'm going to go ahead and just save this.
And this is going to be our 6.1 aggregates example.
Just so I don't lose it I'll just hit save okay.
And so count is pretty easy to understand.
You can use it with your wear filters and you can use n.
You can use between like anything like that.
So let's take a look at the next one.
And the next one is going to be some.
And basically that is going to be the arithmetic sum of whatever list of values that we give it.
And so let's go take a look at employees.
So I'll just right click and select all the rows.
We have 75 employees and they have a salary.
And they also have a department.
So let's ask some more questions.
Let's say we want to know select some.
And in this case we're not going to put a star.
So we want to sum up the salary.
So I'll just go down here and get it.
So here's salary.
Select the sum of the salary from and I'll just drag over employees okay.
So this is all employees.
And that's a lot okay.
So let's say first we'll go ahead and put our comments in here.
What is the sum of our annual payroll which is the sum of all salaries.
And I like to put the problems in plain language like this, because that's usually how you get the
request.
Let's refine this a little bit further.
So I'm just going to copy this and we'll put where.
And we want to look maybe at their department ID equals and 100.
And so if we do this then basically we are going to get the sum of employees who work in this department.
So sum is pretty simple.
We just have to get our air clause to refine the results to exactly what we want.
So I'll go ahead and update my comments here.
What is sum of annual payroll for Department 100?
And so I think once you know how to use some, you know how to use the other ones.
So average is going to return the average of all the values.
And so we can just copy this and change sum to average.
And our average salary is $50,416.
And again of course we could alias like that and make our results look a little bit cleaner.
Okay.
And of course we could do our minimum salary.
So I'll just update the header.
And we can also do our max salary.
And then I can just highlight all of these execute it.
So our average is 50,000 men is 24 Max is 138.
And of course if I wanted to get all this information in just one query, I could combine these basically
as columns.
So I could do average salary.
And then I'll just copy this, put a comma.
There's my men salary.
Put another comma.
There's my max salary.
And then I could just do this.
And we've got a single query that gives us our average our minimum and our maximum.
So a couple of other tips about aggregate functions your sum and your average.
These are always numeric.
However, your min and your max can be used for things like dates or character based data.
And when you use min and max with character based data, it's going to convert it to Ascii and do that.
So let's take a look at a couple of examples there.
Maybe we'll go look at our orders table.
So I'm just going to do select rows.
And it's got some dates here.
So we could use that on our min and max function.
So going back here let's say we want to ask the question what is the minimum date in our orders table.
And so I could just select min and we'll go to our orders table.
Look at our columns.
And here's our order date.
So here's our minimum order date from orders.
So it looks like it was January 3rd of 2003.
Let's just call this earliest order.
And of course we could also get the max date here.
This would be our last order here.
So your min and max can be used with dates.
And it can also be used with names.
So if I did something like select min and I'm going to go back to my employees table, I'm just going
to grab first name and I'll do Max first name from and I will do from our employees.
Let's see what that gives us.
And so so you can see it's going in alphabetical order.
So you could use it for that type of operation if you wanted.
One of the apparent limitations of using aggregate is the aggregate is limited to one value.
And I can't have multiple aggregates in a query.
But each one of these is going to return one value.
And so what if you are asked the question what is the average salary of employees in all departments?
Now we know how to do an average salary, say, in one of our departments where we have a Where clause
where department ID equals 100.
But what if we wanted the output to be the average salary for department 100 average for 200, 304 hundred.
And this is where we can use the group by clause.
And so the group by clause basically allows us to put another column name in here, which is going to
be department ID, and I'll do as department.
Now I can't run it like this because it doesn't know what to do with that aggregate.
So what we want to do is we want to group by our non aggregate fields, which is going to be department
ID.
And what this does is it will give us all departments and calculate the average salary of each department.
So in this way we're able to return multiple rows just by using the groupby.
And we can use multiple non-aggregate columns as long as they are in our group by statement.
So I will put the question up here.
What is the average salary for each department.
And this is the solution to that.
So let's look at another example.
Let's say the question is how many employees do live in each state.
So here we will do select state ID and then count as employees.
And then we're going to group it by each state.
So we'll just say group by state ID.
And when we execute this we see we've got three California residents, three Georgia residents, 64
Massachusetts and five in Texas.
Now let's say that we are going to further expand upon this.
So I'm just going to copy this kind of a typo there.
I take care of that.
And let's say we're going to extend this to state and zip code.
Then we could do I have to go back and look at our table.
And it's going to be just the column name is zip.
So I could just do zip.
And then I have to also add that to my group by statement.
And when we execute that.
So it's going to list every zip code in each state.
And it's going to give us the number of residents in that zip code.
And then once it's done with the state then we'll go down to the next state.
There's only one zip code.
All three people live in it.
Same with Texas.
And then we have two zip codes in California.
So one less concept for this lecture.
And we're going to go back to our first group by example.
And basically what this does is it gives us the average salary for each department.
So it's still basically turning many rows into one by generalizing the data.
In this case it is averaging the salaries by department.
And so we put in our group by department.
So let's take that a little further.
And let's say that you wanted to only show departments and their average salaries, as long as the average
salary was say more than 45,000, we could try to just put where average salary is greater than 45,000.
But notice that where is kind of got the red squiggly under it and we're going to get an error.
And it's basically saying the syntax is incorrect.
Basically, once you put down a group by clause then where is no longer valid.
And that's because where works on individual rows returned by a Select statement.
But we've already consolidated the information for our department and average salary by using groups.
So we can magically fix that by using the having directive.
And basically the way I think about it is having is where for groups.
And so it will look at each department ID and give it a yes or no based on whether the group average
salary value is meeting this predicate, which is greater than 45,000.
So if we execute this, then we're able to interrogate each group and filter out the results so that
we have only the department's return who have an average salary that is greater than 45,000.
So aggregate functions are powerful, but they're pretty simple.
Coming up, we're going to do an assignment where you will get some practice using aggregate functions
in queries.
See you in the next assignment.