SQL Series (eight)--grouping (group by)

In many scenarios, it is necessary to group the data by a certain condition to count its number, average, and so on. With this requirement, SQL naturally has a solution.

The result is grouped by a condition in SQL by a GROUP BY clause. Grammar:

select count(column1), columnJ from table_name group by columnJ;

Group BY is a column or an expression that is followed by a retrieval. Represents the grouping of data for the column or expression, and the same group of data on that column or expression.

Note points used by the group by:

• Group by must function on a search column or on an expression
• Group by can function on multiple columns, grouped by layer in the order of the columns
• Column after group BY is a column or expression in the clause of SELECT
• Group by will be listed as a null value as a set of

Note: Most scenarios use Group by to group by, and then aggregate statistics for grouped data. It is rarely the data that is grouped after the individual columns in a group are taken. Such as:

Select name, age from teacher group by age;

A non-aggregated column name is included in the SELECT clause (this is the data case for the individual columns in the grouping result).
In MySQL default mode: only_full_group_by, if you execute the above SQL will be an error, you can turn it off, if you perform the above Sql,mysql will take the first row of each group of data in the grouping as the result set.

Look at the following example to familiarize yourself with the following group by:

select age, count(name) as group_count from teacher group by age;

The meaning of the above SQL representation: The number of teachers per age is counted by age group, the results of the execution:

Age

	Group_count
25	2
26	2
27	1
28	1
29	1

Let's look at one more example:

select name, max(age), min(age) from person group by name;

The above SQL, according to the name group statistics, find out the maximum age in each group, the minimum age of people, the results are as follows

name	Max	min
Xiaohei	20	29
Xiaohong	28	26
Xiaolan	67	56

The above example is sufficient to understand group by. Group BY is a conditional grouping of the result set, which is then often aggregated.
However, it is often necessary to filter the results after grouping, and SQL uses a different clause to filter the group by results, as follows:

select age, count(name) as group_count from teacher group by age having age < 27;

Execution results

Age

	Count
25	2
26	2

Filter the grouped results to retrieve only groups with age less than 27 in the group.

When you see the having, you are bound to the WHERE clause, and the following summarizes their similarities and differences:

same Point	1.having and where are filtered according to the conditions of the results; 2. Have (infrequently used) in the place where it is used;
Different points	1. For the filter data is not the same, having is mainly used to filter the group, where the main filter table data rows; The position of the 2.having and the WHERE clause is different, having the group by, where after the FROM clause; 3.having is usually used in conjunction with group by;

Is it possible to use where in the case of having a having filter?
According to the above table in the different points 1 can be seen, is possible. Where is the data row filter, and then the filtered rows are grouped, and then the group has a having filter.

The following table lists the order in which SQL is executed:

clauses	Description	Usage Scenarios
Select	Querying data	Used in DB data retrieval
From	The retrieved table	Use when retrieving
where	Conditions for filtering data	When the data needs to be filtered
Gropu by	Grouping the result set of a Select	Commonly used when aggregating statistics is required
Having	Filter grouping	To filter a group, you need to
ORDER BY	Sort result set	Need to show the data in some order

Reference

"SQL must know must be"

SQL Series (eight)--grouping (group by)