In mysql group by and having share precautions

group by function should use should be SELECT every column 1) specified in the list must also appear in the GROUP BY clause, unless this column is used to aggregate function, but today to help your colleagues debugging a mysql group by function, to my surprise, the feeling incredible, and then come back to do a simplified version of the test, test process is as follows:

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, 1 total select id,count(1),aa, the results group by according to the rule, except the aggregate function (count(1)), the other two columns (id,aa) should be included in group by, but the experiment only includes id.

Description of test results
1. The count (1) statistics contained in the id column following group by are correct
2. According to normal thinking, the data of aa cannot be displayed, but mysql chose the first item of aa data in the display table
3, the above 2 is also personal speculation, has not found the official explanation

mysql group by having

group by is grouped according to different fields, and the values can be summarized

For example, the database has A table, including three fields: student, subject, and grade
The database structure is
Students' academic performance
Zhang 3 Chinese 80
Zhang 3 math 100
Li 4 Chinese 70
Li 4 math 80
Li 4 English 80

then
select students,sum(grades) from A group by students;
You get the following result

Student achievement
A 3, 180
Li 4 230

==============================================================

If you think about having
The statement is written:
select students,sum(grades) from A group by students having =80;
And that's what you get

Student achievement
A 3, 80
Li 4 160

having is easier to understand than JOIN ON.

Notes for the use of group by having:

GROUP BY:

One rule of group by is that all columns following select that do not use aggregate functions must appear after group by.

Such as:

select name,sum(point) from table_name 

In this case, the sql statement will report an error. It must be written as:

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 the summing function and the result condition cannot be tested without HAVING.

select name,sum(point) 
from table_name GROUP BY name 
HAVING sum(point)>1000 

having is usually used in combination with group by.