Course Module 4. Working with databases - Lecture: Aggregate functions

6.1 List of aggregate functions

When you use row grouping in SQL with the operator GROUP BY, you can use SELECTfunctions 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

This is not a complete list of aggregate functions, but the rest are very specific and I don't think that you will use them in the next 5 years. If you still need them, you can always read the official documentation for your DBMS.

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, MAXand 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 :)