Example tutorial on the use of sum function in MySQL

Time:2021-12-29

Article introduction

Today, let’s share the use of sum function in MySQL. This function has become a commonly used function when you operate MySQL database. This function counts the sum of the specified columns in the rows that meet the conditions. I’m sure you all know it. There’s nothing to talk about in itself. This article is mainly to deeply understand this function through several small cases and how to use sum function for optimization in MySQL query.

Syntax analysis

SUM([DISTINCT] expr) [over_clause]

  • Returns the sum of expr. If the return set has no rows, SUM() returns NULL. The DISTINCT keyword can be used to sum only the distinct values of expr.
  • If there are no matching rows, SUM() returns NULL.
  • This function executes as a window function if over_clause is present.

The above sentences are a function description of MySQL official documents. Here translate the general meaning.

  • Returns the sum of the expr expression. Null if no rows are returned. Distinct here is to remove duplicate values in the expression expr.
  • If no rows are matched, the function also returns null.
  • If over is set_ Clause, the function is executed as a window function. If you are not familiar with window functions, you can learn about window functions in MySQL.

Function interpretation

When using this function, we should think about how the function counts the sum in the expression? Some programmers may think that this function directly counts the sum of all rows that meet the conditions. In fact, this understanding is correct, but the expression is not very accurate or he does not really understand the operation principle.
In fact, the function is accumulated one by one according to the values of the matching rows. For example, in an order table, there are 10 rows of data that meet the conditions. We need to count the total price in the order. The initial value of sum is 0. When the first row is matched, the order price is 10. At this time, sum becomes 10. When the second row is matched, the order price is 20. At this time, sum is 30. In the third line, the order price is 50, and sum is 80. Accumulate in this way.

Number of rows Order price Sum value
first line 10.00 10.00
Second line 20.00 30.00
Third line 30.00 60.00
Fourth line 40.00 100.00
The fifth line 50.00 150.00
Number that ‘s ok
Line 10 100.00 550.00

Example demonstration

Suppose there is a delivery table with the following structure:


+-----------------------------+---------+
| Column Name   | Type |
+-----------------------------+---------+
| delivery_id   | int |
| customer_id   | int |
| order_date   | date |
| customer_pref_delivery_date | date |
| order_money   | decimal |
+-----------------------------+---------+

delivery_ ID is the primary key of the table.

This table holds the customer’s food delivery information. The customer places an order on a certain date and specifies a desired delivery date (the same as or after the order date). If the customer’s expected delivery date is the same as the order date, the order is called “immediate order”, otherwise it is called “planned order”.
There are the following data:


+-------------+-------------+------------+-----------------------------+-------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date | order_money |
+-------------+-------------+------------+-----------------------------+-------------+
| 1  | 1  | 2019-08-01 | 2019-08-02   | 1.23 |
| 2  | 5  | 2019-08-02 | 2019-08-02   | 1.01 |
| 3  | 1  | 2019-08-11 | 2019-08-11   | 1.09 |
| 4  | 3  | 2019-08-24 | 2019-08-26   | 1.00 |
| 5  | 4  | 2019-08-21 | 2019-08-22   | 10.00 |
| 6  | 2  | 2019-08-11 | 2019-08-13   | 12.09 |
+-------------+-------------+------------+-----------------------------+-------------+

Example 1

Count the total amount of all orders in this table. It’s very simple here. It’s also a common way to use it. Just fill in a column directly with the sum () parameter. According to the above syntax rules, it should be an expression. In fact, a column is also an expression.


select sum(order_money) from Delivery;

Example 2

Write an SQL query statement to obtain the percentage of immediate orders, with two decimal places reserved. This may be a little difficult, because you rarely use this grammar. If you use it, you will find it very simple.

First of all, you should understand that since you are querying the proportion of a certain type, you must count the total. We can count the sum of orders sum1, then the sum of real-time orders sum1, then the sum of real-time orders sum1, and then the sum of real-time orders sum2. Then divide it. However, an SQL statement is explicitly required here. So how to solve it?

Here, you need to understand the operation principle of sum deeply. We can imagine that we can read data line by line, and then accumulate sum line by line. Can we get the synthesis of sum2? As for the synthesis of sum2? As for the synthesis of sum2? As for sum1, it must be very good statistics. It is directly the synthesis of the number of rows in the table.

Solution 1:


select round (
 sum(case when order_date = customer_pref_delivery_date then 1 else 0 end) /
 count(*) * 100,
 2
) as immediate_percentage
from Delivery

Problem solving method 2:


select round (
 sum(order_date = customer_pref_delivery_date) /
 count(*) * 100,
 2
) as immediate_percentage
from Delivery

Here, when the sum expression meets the conditions, it returns 1. Each time a row of data that meets the conditions is read, sum will add 1. Finally, sum will be added after the data is read. Therefore, the total number of pieces that meet the conditions comes out.

Topic source

The title is from leetcode.

Source: leetcode

Link: leetcode CN com/problems/im…

There is also a good example. It is recommended to interpret it well. Deepen the usage scenario of this function.

https://www.jb51.net/article/207813.htm

summary

This is the end of this article on the use of sum function in MySQL. For more information about the use of sum function in mysql, please search the previous articles of developpaer or continue to browse the relevant articles below. I hope you will support developpaer in the future!