group by means "group query", which can be understood as "grouping (Group) according to (by) certain rules"; its function is to divide a data set into several small ones through certain rules area, and then perform data processing on several small areas. In MySQL, GROUP BY is an optional clause of the SELECT statement that is used to group query results based on one or more fields. It reduces the number of rows in the result set.
The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.
group by means "group query".
The Group By statement literally means "group (Group) according to (by) certain rules" in English. Its function is to divide a data set into several small areas through certain rules, and then perform data processing on several small areas.
In MySQL, the GROUP BY keyword can group query results based on one or more fields.
The GROUP BY clause groups a set of rows into a small group of summary row records by the value of a column or expression. The GROUP BY clause returns one row for each group. In other words, it reduces the number of rows in the result set.
The GROUP BY clause is often used with aggregate functions such as SUM, AVG, MAX, MIN and COUNT. Aggregate functions are used in the SELECT clause to calculate information about each grouping.
The GROUP BY clause is an optional clause of the SELECT statement. The following is the GROUP BY clause syntax:
SELECT c1, c2,..., cn, aggregate_function(ci) FROM table WHERE where_conditions GROUP BY c1 , c2,...,cn;
The GROUP BY clause must appear after the FROM and WHERE clauses. Following the GROUP BY keyword is a comma-separated list of columns or expressions that are to be used as conditions to group rows.
1. Simple MySQL GROUP BY example
Let’s take a look at the sample database (yiibaidb ), its structure is as follows -
mysql> desc orders;
Assuming that you want to group the values of the order status into subgroups, you need to use the GROUP BY clause and specify Perform grouping by status column, query as follows:
SELECT status FROM orders GROUP BY status;
Execute the above query statement and get the following results-
You can see that the GROUP BY clause returns The status value is unique. It works like the DISTINCT operator as shown in the following query:
SELECT DISTINCT status FROM orders;
Execute the above query statement and get the following results-
2 , MySQL GROUP BY and aggregate functions
You can use aggregate functions to perform calculations on a group of rows and return a single value. The GROUP BY clause is often used with aggregate functions to perform calculations for each group and return a single value.
For example, if you want to know the number of orders in each status, you can use the COUNT function and the GROUP BY clause query statement, as shown below:
SELECT status, COUNT(*) AS total_number FROM orders GROUP BY status;
Execute the above query statement and get the following results -
Please refer to the following orders (orders) and order details (orderdetails) tables, their ER diagram is as follows-
To get the total amount of all orders by status, you can use the orderdetails table to connect the orders table, and use the SUM function to calculate the total amount. Please refer to the following query:
SELECT status, SUM(quantityOrdered * priceEach) AS amount FROM orders INNER JOIN orderdetails USING (orderNumber) GROUP BY status;
Executing the above query, you get the following results -
Similarly, the following query returns the order number and the total amount of each order .
SELECT orderNumber, SUM(quantityOrdered * priceEach) AS total FROM orderdetails GROUP BY orderNumber;
Execute the above query and get the following results-
3. MySQL GROUP BY expression example
In addition to columns, rows can be grouped by expressions. The following query gets the total sales per year.
SELECT YEAR(orderDate) AS year, SUM(quantityOrdered * priceEach) AS total FROM orders INNER JOIN orderdetails USING (orderNumber) WHERE status = 'Shipped' GROUP BY YEAR(orderDate);
Execute the above query and get the following results -
In this example, we use the YEAR function to extract year data from the order date (orderDate). Only orders with Shipped status are included. Note that the expressions appearing in the SELECT clause must be the same as those in the GROUP BY clause.
You can use the HAVING clause to filter the groups returned by the GROUP BY clause. The following query uses the HAVING clause to select annual sales totals from 2013 onwards.
SELECT YEAR(orderDate) AS year, SUM(quantityOrdered * priceEach) AS total FROM orders INNER JOIN orderdetails USING (orderNumber) WHERE status = 'Shipped' GROUP BY year HAVING year > 2013;
Execute the above query and get the following results -
标准SQL不允许使用GROUP BY子句中的别名,但MySQL支持此选项。以下查询从订单日期提取年份,并对每年的订单进行计数。该year用作表达式YEAR(orderDate)的别名,它也用作GROUP BY子句中的别名,此查询在标准SQL中无效。
参考以下查询 -
SELECT YEAR(orderDate) AS year, COUNT(orderNumber) FROM orders GROUP BY year;
执行上面查询,得到以下结果 -
MySQL还允许您以升序或降序(标准SQL不能提供)对组进行排序。默认顺序是升序。例如,如果要按状态获取订单数量并按降序对状态进行排序,则可以使用带有DESC的GROUP BY子句,如下查询语句:
SELECT status, COUNT(*) FROM orders GROUP BY status DESC;
执行上面查询,得到以下结果 -
请注意,在GROUP BY子句中使用DESC以降序对状态进行排序。我们还可以在GROUP BY子句中明确指定ASC,按状态对分组进行升序排序。
【相关推荐:mysql视频教程】
The above is the detailed content of What does group by mean?. For more information, please follow other related articles on the PHP Chinese website!