Data grouping and aggregate functions

Operator GROUP BY

Sometimes you need to find out information not about the objects themselves, but about the specific groups that they form. To do this, use the GROUP BY operator and aggregate functions.

The General structure of the query with GROUP BY

MySQL

SELECT [constants, aggregate_function, grouping_fields] 
FROM table_name 
GROUP BY grouping_fields;

Example

To demonstrate the work of the GROUP BY operator, we derive the total amount of money spent, grouped by family members.

MySQL

SELECT family_member, SUM(unit_price * amount) FROM Payments 
GROUP BY family_member;

Below is the data set in the Payments table:

family_member	unit_price	amount
1	2000	1
2	2100	1
3	20	5
4	350	1
4	150	2
5	100	1
2	120	1
2	5500	1
5	230	1
5	230	1
5	250	1
5	250	1
3	2200	1
2	66000	1
1	8	5
1	7	5
2	8	3
1	8	8
1	7	5
2	150	3
3	100	1
1	10	3
1	300	1
3	150	8

When the request is executed, the grouping is done by the family_member field and the total amount spent on purchases by each family member is added up.

In order to better understand how grouping by field occurs, we transform the source data into a new, grouped view:

family_member	unit_price	amount
1	2000	1
	300	1
	10	3
	7	5
	8	8
	7	5
	8	5
2	2100	1
	120	1
	5500	1
	150	3
	8	3
	66000	1
3	150	8
	100	1
	2200	1
	20	5
4	150	2
	350	1
5	250	1
	250	1
	230	1
	230	1
	100	1

As you can see, groups of records have been formed, united by onefamily_member. After that, we can apply the sum formula inside each of these groups, which will multiply the quantity of the product by its value, and then add up all the resulting values:

family_member	SUM(unit_price * amount)
1	2504
2	74194
3	3600
4	650
5	1060

It should be borne in mind that for GROUP BY all NULL values are treated as equal, i.e. when grouped by a field containing NULL values, all such rows will fall into one group.

Aggregate functions

An aggregate function performs a calculation on a set of values and returns a single value.

Aggregate functions are used for non-null values. The exception is the COUNT() function.

Function	Description
SUM(поле_таблицы)	Returns the sum of the values
AVG(поле_таблицы)	Returns the average value
COUNT(поле_таблицы)	Returns the number of records
MIN(поле_таблицы)	Returns the minimum value
MAX(поле_таблицы)	Returns the maximum value

HAVING operator

To filter rows by values of aggregate functions, the HAVING operator is used.

General query structure with the HAVING operator

MySQL

SELECT [constants, aggregate_function, grouping_fields] 
FROM table_name 
GROUP BY grouping_fields
HAVING conditions_for_selection_restrictions_after_grouping

Difference between HAVING and WHERE

WHERE — records are selected by condition first, and then can be grouped, sorted, etc.
HAVING — records are grouped first, and then selected by condition, while, unlike WHERE, you can use the values of aggregate functions in it

Example of use

Display the total amount spent on purchases for each family member, where the total purchase amount is less than 5000 rubles:

MySQL

SELECT family_member, SUM(unit_price * amount) AS sum 
FROM Payments GROUP BY family_member
HAVING sum < 5000);

Data grouping and aggregate functions

Operator GROUP BY

The General structure of the query with GROUP BY

Example

Aggregate functions

HAVING operator

General query structure with the HAVING operator

Example of use

Tasks for self-testing