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

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.

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

Below is the data set in the Payments table:

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:

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:

Aggregate functions

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

HAVING operator

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

General query structure with the HAVING operator

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:

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