Considerations for using group by and having in mysql

In mysql, group by group queries are often used and used together with having. The following describes the precautions for using group by and having, I hope this tutorial will be helpful to all of you.

The use of the group by function should be that each column specified in the SELECT list must also appear in the group by clause, unless this column is used for Aggregate functions, but today I was surprised to help my colleagues debug a group by function in mysql. At that time, I felt incredible. Then I came back to do a simplified version test. The test process is as follows:

Mysql table structure

The Code is as follows:	Copy code
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

The Code is as follows:	Copy code
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

The Code is as follows:	Copy code
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

The Code is as follows:	Copy code
Select name, sum (point) from table_name

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

The Code is as follows:	Copy code
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.

The Code is as follows:	Copy code
Select name, sum (point) From table_name group by name HAVING sum (point)> 1000

Having is usually used together with group.