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.