## 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 function`SUM()`

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

GO TO FULL VERSION