The GROUP BY clause groups query results.
Syntax:
GROUP BY supports grouping by a single field or multiple fields. You can also use the WHERE clause to filter data before grouping, use the HAVING clause to filter data after grouping, and use the ORDER BY clause to sort data after grouping. Syntax for the GROUP BY clause:
SELECT select_list FROM table_list
[WHERE query_condition]
GROUP BY group_by_expression
[HAVING group_condition];
select_list:
column_name, group_function,...
Parameters in the syntax:
group_function: the aggregate function.group_by_expression: the group expression. Separate multiple expressions by commas (,).group_condition: specifies to filter data after it is grouped.
In a GROUP BY query, the columns following the SELECT statement must be the columns following the GROUP BY clause or the columns specified by aggregate functions.
The following table lists frequently-used aggregate functions in GROUP BY queries.
| Aggregate functions | Description |
|---|---|
| MAX() | Queries the maximum value of the specified column. |
| MIN() | Queries the minimum value of the specified column. |
| COUNT() | Returns the number of rows in the query result. |
| SUM() | Returns the sum of the specified column. |
| AVG() | Returns the average value of the data in the specified column. |
Examples
Create a table named fruit_order and insert proper data.
CREATE TABLE fruit_order(
order_id INT NOT NULL AUTO_INCREMENT COMMENT 'Order ID',
user_id BIGINT NOT NULL COMMENT 'Customer ID',
user_name VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'Customer name',
fruit_price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 'Order amount',
order_year SMALLINT NOT NULL COMMENT 'Year of orders',
PRIMARY KEY (order_id)
) COMMENT 'Order table';
INSERT INTO fruit_order(order_id,user_id,user_name,fruit_price,order_year) VALUES
(1,1011,'Zhang San',13.11,'2019'),
(4,1011,'Zhang San',22.21,'2020'),
(6,1011,'Zhang San',58.83,'2020'),
(2,1022,'Li Si',23.34,'2019'),
(3,1022,'Li Si',12.22,'2019'),
(7,1022,'Li Si',14.66,'2021'),
(8,1022,'Li Si',34.44,'2021'),
(5,1033,'Wang Wu',51.55,'2020'),
(9,1033,'Wang Wu',63.66,'2021');
GROUP BY query based on a single field
Query the number of orders placed by each customer and print the customer ID and the number of orders.
obclient> SELECT user_id Customer ID, COUNT(order_id) Number of orders FROM fruit_order GROUP BY user_id;
+----------+--------------+
| Customer ID | Number of orders |
+----------+--------------+
| 1011 | 3 |
| 1022 | 4 |
| 1033 | 2 |
+----------+--------------+
3 rows in set
GROUP BY query based on multiple fields
Query the number of orders placed by each customer each year and print the customer ID, the year of order placement, and the number of orders.
obclient> SELECT user_id Customer ID, order_year Year of order placement, COUNT(order_id) Number of orders FROM fruit_order GROUP BY user_id,order_year;
+----------+--------------+--------------+
| Customer ID | Year of order placement | Number of orders |
+----------+--------------+--------------+
| 1011 | 2019 | 1 |
| 1011 | 2020 | 2 |
| 1022 | 2019 | 2 |
| 1022 | 2021 | 2 |
| 1033 | 2020 | 1 |
| 1033 | 2021 | 1 |
+----------+--------------+--------------+
6 rows in set
Filter data before grouping
Query the number of orders placed by each customer in 2020 and print the customer ID and the number of orders.
obclient> SELECT user_id Customer ID, COUNT(order_id) Number of orders FROM fruit_order t WHERE t.order_year = 2020 GROUP BY user_id;
+----------+--------------+
| Customer ID | Number of orders |
+----------+--------------+
| 1011 | 2 |
| 1033 | 1 |
+----------+--------------+
2 rows in set
Filter data after grouping
Query customers who placed two or more orders in 2019 and print the customer IDs and the numbers of orders.
obclient> SELECT user_id Customer ID, COUNT(order_id) Number of orders FROM fruit_order t WHERE t.order_year = 2019 GROUP BY user_id HAVING COUNT(order_id)>=2;
+----------+--------------+
| Customer ID | Number of orders |
+----------+--------------+
| 1022 | 2 |
+----------+--------------+
1 row in set
The preceding sample code is equivalent to:
obclient> SELECT user_id Customer ID, COUNT(order_id) Number of orders FROM fruit_order t WHERE t.order_year = 2019 GROUP BY user_id HAVING Number of orders >= 2;
+----------+--------------+
| Customer ID | Number of orders |
+----------+--------------+
| 1022 | 2 |
+----------+--------------+
1 row in set
Note
If a
GROUP BYquery contains theHAVINGclause, the data is first grouped and then filtered based on the conditions specified inHAVINGclause. Aggregate functions can be used after theHAVINGclause is executed. These aggregate functions may be different from those following theSELECTstatement.
Sort data after grouping
Query the maximum order amount of each customer and print the customer IDs and maximum order amounts by maximum order amount in descending order.
obclient> SELECT user_id Customer ID, MAX(fruit_price) Maximum order amount FROM fruit_order t GROUP BY user_id ORDER BY Maximum order amount DESC;
+----------+--------------+
| Customer ID | Maximum order amount |
+----------+--------------+
| 1033 | 63.66 |
| 1011 | 58.83 |
| 1022 | 34.44 |
+----------+--------------+
3 rows in set