What does group by mean?

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.

MySQL uses GROUP BY group query

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.

MySQL GROUP BY example

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.

MySQL GROUP BY and HAVING 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 -

GROUP BY子句：MySQL与标准SQL

标准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!