Detailed explanation of the problem of mixed use of limit and sum functions in MySQL

Preface

Today, after synchronizing the order data, a colleague chose to use the LIMIT and SUM() functions to calculate the total amount of the current page to compare the total amount of a specific order with the other party because there was a difference between the total order amount and the total amount of the data source. However, he found that the calculated amount was not the total amount of the paged orders, but the total amount of all orders.

The database version is MySQL 5.7. Below we will use an example to review the problem encountered.

Problem review

This review will use a very simple order table as an example.

Data preparation

The order table creation statement is as follows (I am lazy here and use the auto-increment ID. It is not recommended to use the auto-increment ID as the order ID in actual development)

CREATE TABLE `order` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Order ID',
 `amount` decimal(10,2) NOT NULL COMMENT 'Order amount',
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The SQL to insert an amount of 100 is as follows (execute 10 times)

INSERT INTO `order`(`amount`) VALUES (100);

So the total amount is 10*100=1000.

Problem SQL

Use limit to query the data in pages, and use the sum() function to calculate the total amount of the current page

SELECT 
  SUM(`amount`)
FROM
  `order`
ORDER BY `id`
LIMIT 5;

As mentioned above, the expected result is 5*100=500, but the actual result is 1000.00 (the decimal point is due to the data type).

Troubleshooting

In fact, if you have a certain understanding of the execution order of SELECT statements, you can quickly determine why the returned result is the total amount of all orders? Next, I will analyze the problem based on the execution sequence of the problematic SQL:

1. FROM: The FROM clause is executed first, which determines that the query is the order table.
2. SELECT: The SELECT clause is the second clause executed, and the SUM() function is also executed at this time.
3. ORDER BY: The ORDER BY clause is the third clause executed, and it only has one result, which is the total amount of the order.
4. LIMIT: The LIMIT clause is executed last, and at this time there is only one result in the result set (the total amount of the order)

Supplementary content

Here is the execution order of SELECT statement

1. FROM <left_table>
2. ON <join_condition>
3. <join_type> JOIN <right_table>
4. WHERE <where_condition>
5. GROUP BY <group_by_list>
6. HAVING <having_condition>
7. SELECT
8. DISTINCT <select_list>
9. ORDER BY <order_by_condition>
10. LIMIT <limit_number>

Solution

When you need to count paging data (in addition to the SUM() function, the common COUNT(), AVG(), MAX(), and MIN() functions also have this problem), you can choose to use a subquery to handle it (PS: memory calculation is not considered here, and the purpose is to use the database to solve this problem). The solution to the above problem is as follows:

SELECT 
  SUM(o.amount)
FROM
  (SELECT 
    `amount`
  FROM
    `order`
  ORDER BY `id`
  LIMIT 5) AS o;

The return value of the operation is 500.00.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.