Syntax:
SELECT select_expr [, select_expr ...]
FROM table_name
[WHERE where_condition]
[GROUP BY {col_name | expr} [ASC | DESC], ... [WITH ROLLUP]]
The GROUP BY clause divides the rows of a table into distinct groups for each distinct value of a given column or expression. Use the group function to return statistics for each group.
Specify a column to group
Query the name of each city and the number of players
root@TENNIS 15:52 mysql>SELECT town, count(*) FROM PLAYERS GROUP BY town; + ----------- + ---------- + | town | count(*) | + ----------- + ---------- + | Douglas | 1 | | Inglewood | 2 | | Stratford | 6 | + ----------- + ---------- + 3 rows in set (0.00 sec)
Use multiple grouping columns to form a grouping effect of “subdividing into small groups within a large group”
Count the total number of rounds won by each player on each team
root@TENNIS 15:53 mysql>SELECT teamno, playerno, sum(won) FROM MATCHES GROUP BY teamno, playerno; + -------- + ---------- + ---------- + | teamno | playerno | sum(won) | + -------- + ---------- + ---------- + | 1 | 2 | 1 | | 1 | 6 | 8 | | 1 | 8 | 0 | | 1 | 44 | 3 | | 1 | 57 | 3 | | 1 | 83 | 0 | | 2 | 8 | 0 | | 2 | 27 | 3 | | 2 | 104 | 3 | | 2 | 112 | 3 | + -------- + ---------- + ---------- + 10 rows in set (0.01 sec)
Group by expression
For each year in the PENALTIES table, get the number of fines paid
root@TENNIS 15:54 mysql>SELECT year(payment_date), count(*) FROM PENALTIES GROUP BY year(payment_date); + -------------------- + ---------- + | year(payment_date) | count(*) | + -------------------- + ---------- + | 1980 | 4 | | 1981 | 2 | | 1982 | 1 | | 1983 | 2 | | 1984 | 3 | + -------------------- + ---------- + 5 rows in set (0.00 sec)
Grouping with sorting
You can combine group by and order by clauses if the grouping column and sorting column are the same
Get the number of each team and the total number of games played, and the results are sorted by team number in descending order?
root@TENNIS 15:55 mysql>SELECT teamno, count(*) FROM MATCHES GROUP BY teamno ORDER BY teamno DESC; + -------- + ---------- + | teamno | count(*) | + -------- + ---------- + | 2 | 5 | | 1 | 8 | + -------- + ---------- + 2 rows in set (0.00 sec)
You can include desc (or asc) in the group by clause to simplify
root@TENNIS 15:56 mysql>SELECT teamno, count(*) FROM MATCHES GROUP BY teamno DESC; + -------- + ---------- + | teamno | count(*) | + -------- + ---------- + | 2 | 5 | | 1 | 8 | + -------- + ---------- + 2 rows in set, 1 warning (0.00 sec)
Rules for the GROUP BY clause
1. A single column that appears in the SELECT clause must appear in the GROUP BY clause as a grouping column. This rule applies to other databases, but not to MYSQL. For example:
root@TENNIS 15:57 mysql>SELECT town, count(*) FROM PLAYERS GROUP BY sex; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'TENNIS.PLAYERS.TOWN' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_ full_group_by
Executed in MYSQL, no exception is thrown, but the result is returned. The value of the town column is returned randomly. Force mysql to follow this rule like other databases by setting the value of the sql_mode system variable to ONLY_FULL_GROUP_BY (recommended)
2. Grouping columns may not appear in the SELECT clause
3. Grouping columns can appear in a compound expression in the SELECT clause
Got a list of fines in cents?
root@TENNIS 15:59 mysql>SELECT cast(amount * 100 AS SIGNED INTEGER) AS amount_in_cents FROM PENALTIES GROUP BY amount; + -----------------+ |amount_in_cents| + -----------------+ | 2500 | | 3000 | | 5000 | | 7500 | | 10000 | + -----------------+ 5 rows in set (0.00 sec)
4. If GROUP BY is followed by a compound expression, it must be used as a part of an expression in the SELECT clause.
GROUP_CONCAT() function
MYSQL-specific group functions. The function’s value is equal to all values of the specified column that belong to a group. The values are placed next to each other, separated by commas, and represented as strings.
For each team, get its number and the numbers of all players?
root@TENNIS 15:59 mysql>SELECT teamno, group_concat(playerno)
-> FROM MATCHES
-> GROUP BY teamno;
+ -------- + ------------------------ +
| teamno | group_concat(playerno) |
+ -------- + ------------------------ +
| 1 | 6,6,6,44,83,2,57,8 |
|2|27,104,112,112,8|
+ -------- + ------------------------ +
2 rows in set (0.00 sec)
If there is no group by clause, group_concat returns all values of a column
Get a list of all fine numbers?
root@TENNIS 16:01 mysql>SELECT group_concat(paymentno)
-> FROM PENALTIES;
+ ------------------------------------+
| group_concat(paymentno) |
+ ------------------------------------+
|2,3,4,5,6,7,8,9,102,103,104,109|
+ ------------------------------------+
1 row in set (0.00 sec)
The system variable group_concat_max_len controls the maximum character length returned by this function, and the default is 1024. It can be modified by the set statement:
Set @@group_concat_max_len=7;
Use the WITH ROLLUP clause
Used to require multiple different groupings in one GROUP BY clause
Get the number of each player, the sum of penalties and the sum of all players’ penalties?
root@TENNIS 16:01 mysql>SELECT playerno, sum(amount)
-> FROM PENALTIES
-> GROUP BY playerno
-> UNION ALL
-> SELECT NULL, sum(amount)
-> FROM PENALTIES;
+ ---------- + ------------- +
| playerno | sum(amount) |
+ ---------- + ------------- +
| 6 | 200.00 |
| 8 | 25.00 |
| 27 | 350.00 |
| 44 | 205.00 |
| 104 | 50.00 |
| NULL | 830.00 |
+ ---------- + ------------- +
6 rows in set (0.01 sec)
The first query groups by each player, the second query groups by all players
Rewrite the above example:
root@TENNIS 16:03 mysql>SELECT playerno, sum(amount)
-> FROM PENALTIES
-> GROUP BY playerno WITH ROLLUP;
+ ---------- + ------------- +
| playerno | sum(amount) |
+ ---------- + ------------- +
| 6 | 200.00 |
| 8 | 25.00 |
| 27 | 350.00 |
| 44 | 205.00 |
| 104 | 50.00 |
| NULL | 830.00 |
+ ---------- + ------------- +
6 rows in set (0.00 sec)
with rollup indicates that after grouping by playerno, another grouping is required.
In general, if there is a clause GROUP BY E1,E2,E3,E4 WITH ROLLUP, then the following groupings will be performed respectively: [E1,E2,E3,E4], [E1,E2,E3], [E1,E2] , [E1], []. [] means that all rows are grouped together.
Count the total number of players according to their gender and city of residence; count the total number of players of each gender; count the total number of all players?
root@TENNIS 16:03 mysql>SELECT sex, town, count(*)
-> FROM PLAYERS
-> GROUP BY sex,town WITH ROLLUP;
+ ----- + ----------- + ---------- +
| sex | town | count(*) |
+ ----- + ----------- + ---------- +
| F | Inglewood | 1 |
| F | NULL | 1 |
| M | Douglas | 1 |
| M | Inglewood | 1 |
| M | Stratford | 6 |
| M | NULL | 8 |
| NULL | NULL | 9 |
+ ----- + ----------- + ---------- +
7 rows in set (0.00 sec)
Filter the grouped results
Grouped results cannot be filtered using the WHERE clause
Group functions cannot be used in the WHERE clause
Example: Get the numbers of those players who were penalized more than once
root@TENNIS 16:05 mysql>SELECT playerno FROM PENALTIES WHERE count(*) > 1 GROUP BY playerno; ERROR 1111 (HY000): Invalid use of group function
Because the WHERE clause is executed before the GROUP BY, and the group function must be executed after the group is divided.
HAVING clause
grammar:
SELECT select_expr [, select_expr ...]
FROM table_name
[WHERE where_condition]
[GROUP BY {col_name | expr} [ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
Specially used to filter the grouped results
HAVING can be used alone without GROUP BY
Group functions can be used in the HAVING clause
Example: get the numbers of those players who were penalized more than once
root@TENNIS 16:05 mysql>SELECT playerno
-> FROM PENALTIES
-> GROUP BY playerno
-> HAVING count(*) > 1;
+ ---------- +
| player no |
+ ---------- +
| 6 |
| 27 |
| 44 |
+ ---------- +
3 rows in set (0.00 sec)
If there is only the HAVING clause and no GROUP BY, all rows in the table are grouped together
Example: Get the sum of all fines, and only display when the sum is greater than 250 yuan
root@TENNIS 16:06 mysql>SELECT sum(amount)
-> FROM PENALTIES
-> HAVING sum(amount) > 250;
+ ------------- +
| sum(amount) |
+ ------------- +
| 830.00 |
+ ------------- +
1 row in set (0.00 sec)
Columns in the HAVING clause either appear in a group function or in the GROUP BY clause. otherwise error
root@TENNIS 16:07 mysql>SELECT town, count(*)
-> FROM PLAYERS
-> GROUP BY town
-> HAVING birth_date > '1970-01-01';
ERROR 1054 (42S22): Unknown column 'birth_date' in 'having clause'
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge Cloud native entry skill treeHomepageOverview 14780 people are studying systematically