SQL grouping is used to group similar data in a dataset and to perform certain operations over these groups. Grouping operations help to make the data more meaningful and manageable.

Grouping Functions

The most common grouping functions used in SQL are: sum, avg, count, max, min.

SUM function

This function calculates the sum of numerical values in a column. For example, to find the total salary in the table below;"

SELECT SUM(salary) FROM `persons`

query can be used.

AVG function

This function calculates the average of the numeric values in a column. To find the average salary of the people in the given table, the following query can be used.

SELECT AVG(salary) FROM `persons`

COUNT function

This function counts how many values are in a column. When used with the DISTINCT statement, it calculates the number of unique values. For example, to find out how many people are in the data set, the following query can be used.

SELECT COUNT(*) FROM `persons`

To determine how many countries are in the data set presented in the table:

SELECT COUNT(DISTINCT country) AS CountryNumber FROM `persons`

MAX, MIN functions

These functions calculate the largest and smallest values in a column. When we want to find out the youngest and oldest ages of the people in the table:

SELECT MAX(age) AS maximumage FROM `persons`SELECT MIN(age) AS minimumage FROM `persons`

Group by

Grouping functions are typically used with the GROUP BY statement. As the name suggests, the GROUP BY statement groups. That is, it groups the result set by one or more columns. For example, to display the average salaries by country in the given table:

SELECT country, AVG(salary) FROM `persons` GROUP BY country

Similarly, to calculate average salaries by gender:

SELECT gender, AVG(salary) AS countFROM `persons` GROUP BY gender

In another example, if the average of the salaries by country and gender is desired:

SELECT country, gender, AVG(age), COUNT(*) FROM `persons` GROUP BY country, gender

Setting conditions on groups

In SQL statements, the WHERE block is used for conditional expressions. However, if a condition will be specified on the created groups, in other words, if grouping functions are used in conditional expressions, it should be written in the HAVING block. This process filters the data grouped by a condition after the GROUP BY operation. HAVING cannot be used without the GROUP BY statement.

SELECT country, AVG(salary) FROM `persons` GROUP BY countryHAVING AVG(age) > 30

For example, the query above first calculates the average salaries by country, then presents the average salaries of countries with an average age over 30.

A query that returns the countries where the average salary of male employees is more than 3000 and the average salary as a result:

SELECT country, AVG(salary) FROM `persons` WHERE male=1 GROUP BY countryHAVING AVG(salary) > 3000

The general structure for querying operations on a single table is as follows:

SELECT pick_listFROM table_source [ WHERE condition_expression ] [ GROUP BY grouping_expression ] [ HAVING condition_expression ] [ ORDER BY sort_condition [ ASC | DESC ] ]

In this learning content, we closely examined the operations related to the grouping and summarization of data with examples. In the next learning content, we will examine the content of working with temporary data.

‍