Detailed description of having and group by commands in mysql

You can use the aggregate function count (*) in Mysql to implement this function. For example, to query the records with the most frequently displayed names in the user table, you can group the records by group by name first, use count to calculate the number of entries in the group, and then sort by count. The SQL statement is as follows:

Select name, count (*) from user group by name order by count (*) DESC limit 1;

Data sorted by name is returned without the limit of limit.

Before using the aggregate function, we can use the where clause to add restrictions to the query. What if we need to add restrictions to the data in the group after group? The answer is having.

Having clause allows us to filter the data of each group after grouping.

The where clause filters records before aggregation, that is, before the group by clause and HAVING clause. The having clause filters group records after aggregation.

The having clause must contain data after group by and after order.

After sorting names by the number of occurrences, we want to find only the data containing lin in the name, which can be written as follows:

Select name, count (*) from user group by name having name like '% lin %' order by couny (*) DESC;

Notes for using group by having in mysql:

Group:

The principle of group by is that all columns after select do not use aggregate functions and must appear after group.

For example, select name, sum (point) from table_name

In this way, the SQL statement reports an error and must be written as follows:

Select name, sum (point) from table_name group by name

HAVING

The reason for adding HAVING to SQL is that WHERE cannot be applied to aggregate functions, and if HAVING is not available, it cannot be used to test the results.

Select name, sum (point)
From table_name group by name
HAVING sum (point)> 1000

Example

Mysql table structure

Mysql> desc t;
+ --- + ----- + -- + --- +
| Field | Type | Null | Key | Default | Extra |
+ --- + ----- + -- + --- +
| Id | int (11) | YES | 0 |
| Name | varchar (100) | YES | NULL |
| Aa | varchar (45) | YES | NULL |
+ --- + ----- + -- + --- +
3 rows in set (0.01 sec)

Insert data

Mysql> select * from t;
+ -- + --- +
| Id | name | aa |
+ -- + --- +
| 1 | aaaa | bbbb |
| 1 | 1111 | 2222 |
| 1 | 2222 | 33333 |
| 1 | 2222 | 44444 |
| 2 | 2222 | 44444 |
| 2 | 2222 | 1111 |
| 3 | 2222 | 1111 |
| 1 | 2222 | 44444 |
| 1 | 2222 | 44444 |
| 1 | 2222 | 44444 |
| 3 | 2222 | aaaa |
+ -- + --- +
11 rows in set (0.00 sec)
Group by query statement

Mysql> select id, count (1), aa from t group by id;
+ -- + ---- + --- +
| Id | count (1) | aa |
+ -- + ---- + --- +
| 1 | 7 | bbbb |
| 2 | 2 | 44444 |
| 3 | 2 | 1111 |
+ -- + ---- + --- +
3 rows in set (0.00 sec)

In this experiment, a total of select id, count (1), aa, and result group by follow the rule, except for the aggregate function (count (1), the other two columns (id, aa) should be included in group by, but the test only contains the id.
Description of test results
1. The count (1) statistics of the id column after group by are correct.
2. Normally, aa data cannot be displayed, but mysql selects the first aa data in the display table.
3. The above two are my personal guesses. No official instructions are found for the moment.

Mysql group by having usage

Group by is grouped by different fields, and numerical values can be summarized.
For example, the database contains Table A, which includes three fields: Student, subject, and score.
The database structure is

Student subject score
Zhang San Chinese 80
Zhang San, Mathematics 100
Li Si language 70
Li Si math 80
Li Si English 80
So
Select student, sum (score) from A group by student;
The following result is displayed.
Student Score
Zhang San 180
Li Si 230
========================================================== ======================================
If having
Statement:
Select student, sum (score) from A group by student having score = 80;
The result is as follows:
Student Score
Zhang San 80
Li Si 160
Having is easier to understand than join on.
Notes for using group by having in mysql:
Group:
The principle of group by is that all columns after select do not use aggregate functions and must appear after group.
For example:

Select name, sum (point) from table_name
In this way, the SQL statement reports an error and must be written as follows:

Select name, sum (point) from table_name group by name
HAVING
The reason for adding HAVING to SQL is that WHERE cannot be applied to aggregate functions, and if HAVING is not available, it cannot be used to test the results.

Select name, sum (point)
From table_name group by name
HAVING sum (point)> 1000
Having is usually used together with group.