6.1 List of aggregate functions
When you use row grouping in SQL with the operator GROUP BY
, you can use SELECT
functions in the statement that operate on the grouped data. Such functions are also called aggregate functions.
Here is a list of the most popular ones:
# | Function | Description |
---|---|---|
1 | COUNT() | Returns the number of values in a group |
2 | SUM() | Returns the sum of values in a group |
3 | MAX() | Returns the maximum value of a group |
4 | MIN() | Returns the minimum value of a group |
5 | AVG() | Returns the mean of a group |
6 | BIT_AND() | Performs a bitwise AND over all group values |
7 | BIT_OR() | Performs a bitwise OR over all group values |
8 | BIT_XOR() | Performs a bitwise XOR over all group values |
9 | GROUP_CONCAT() | Concatenates all group values into one string |
Now let's look at some examples with our aggregate functions.
6.2 Analyzing employee salaries
Let's compute some statistics on our employees from the employee table .
Question one: how many employees do we have?
If we want to find out the number of all records in the table, then we can use the aggregate function for this COUNT
. The request will look like this:
SELECT COUNT(*) FROM employee
And MySQL will return the number 6 as a response. We have 6 employees in the department, including a cat. All right.
Question two: how much do we pay per month to all employees?
To answer this question, we need to sum the salaries of all employees. To do this, we use the aggregate functionSUM()
The request will look like this:
SELECT SUM(salary) FROM employee
Note that this time we are required to specify the values of which column we are summarizing. We've specified a salary column . We can't just sum all the fields in a table.
And MySQL will return the number 461000 as an answer. We have 6 employees in the department, and the salary is 461 thousand. Too much.
And finally, the third question: what are our maximum and minimum salaries in the department? Well, let's calculate the average salary. To do this, we need the functions MIN
, MAX
and AVG
.
The query will be a bit more complex this time and look like this:
SELECT MIN(salary), AVG(salary), MAX(salary)
FROM employee
The result of this query will be:
MIN(salary) | AVG(salary) | MAX(salary) |
---|---|---|
1000 | 76833.3333 | 200000 |
The minimum wage in our department is $1,000 – very good. The maximum salary is 200 thousand, but this is the director.
But the average salary is too high, you need to somehow optimize costs. Let's hire another cat and that's it :)