MySQL: Using the Group By Function

Grouping functions include: AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE. I assume you have some general knowledge on how these work (their pretty straight forward), but let us look at a quick example:

SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)

FROM   employees;

In the above example, we are focusing on some arbitrary table called employees. We are using the aggregate functions AVG, MAX, MIN, and SUM on the salary column of the employees table. A table will be returned with 4 columns, named AVG(salary), MAX(salary), MIN(salary), SUM(salary) respectively. Each of these columns will contain a single value under them (the next row so to speak), that will contain the average of the salary row, the maximum value in the salary row, the minimum value in the salary row, and finally the sum of the salary row. It’s not a very difficult concept, but when the GROUP BY clause gets introduced, some get confused. Let’s have a look at the next example which includes the GROUP BY clause. 

SELECT  department_id, AVG(salary)

FROM employees

GROUP BY department_id ;

In this example, we are retrieving two columns from the arbitrary employees table. One of these columns will be detpatment_id, while the next will be the average salary. Now usually, average salary would yield one value (the average of the salary column, a single value). But the group by function changes this. Instead, the AVG function takes the average of each individual department id. So for example, if there were two departments, 1 and 2, the AVG function would take the average of the salary of all the salaries that are from department one, display this value, and then do the same for department number 2. The group by clause acts on only the aggregate functions in the select statements. It performs the aggregate function on the rows which have the same department ID only. 

That should sum up how to use the GROUP BY clause. Of course things can get far more complicated, but this is my basic approach of how I look at using the GROUP BY cause. I hope this tutorial was useful.