Example tutorial on using the sum function in MySQL

Introduction

Today I will share the use of the sum function in MySQL. This function has become a commonly used function when operating MySQL databases. This function counts the sum of specified columns in rows that meet the conditions. I believe everyone must know it, so there is nothing to talk about it. This article mainly uses several small cases to gain an in-depth understanding of this function and how to use the sum function for optimization when doing MySQL queries.

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 from the official MySQL documentation. Here is a translation of what it roughly means.

• Returns the sum of expr. If no row count is returned, NULL is returned. The DISTINCT here is to remove duplicate values ​​in the expression expr.
• If no matching rows are found, this function also returns NULL.
• If over_clause is set, 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 Explanation

When using this function, we should think about how this 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. This understanding is actually not wrong, but it is not expressed very accurately or he does not really understand the operating principles.
In fact, this function accumulates the values ​​of the matching rows one by one. Here is an example. There are 10 rows of data that meet the conditions in an order table. We need to count the total price of the order. The initial value of sum is 0. When matching the first row, the order price is 10, and the sum becomes 10. When matching the second row, the order price is 20, and the sum is 30. The order price in the third row is 50, so the sum is 80. In this way, the numbers are accumulated one by one.

Example Demonstration

Assume there is a table (Delivery) 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 food delivery information for customers who place an order on a certain date and specify 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 an "immediate order", otherwise it is called a "planned order".
The following data is included:

+-------------+-------------+------------+-----------------------------+-------------+
| 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

Calculate the total amount of all orders in the table. This is very simple, and it is also our common usage method. Just fill in a column with the sum() parameter. According to the above grammatical rules, it should be an expression. In fact, a column is also an expression.

select sum(order_money) from Delivery;

Example 2

Write a SQL query to get the percentage of instant orders, keeping two decimal places. This may be a little difficult because you rarely use this syntax. If you have used it, you will find it is very simple.

First of all, you need to understand that since you are querying the proportion of a certain type, you must calculate the total. We can count the total order sum1, then count the total instant order sum1, then count the total instant order sum1, and then count the total instant order sum2. Then just divide it. But here a SQL statement is explicitly required. So how to solve it?

Here you need to have a deep understanding of the operating principle of sum. We can imagine that we read the data line by line, and then let sum accumulate line by line. Can we get the summary of sum2? As for the synthesis of sum2? As for the synthesis of sum2? As for sum1, it is definitely easy to count, as it is the sum 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

Solution 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 condition, 1 is returned. Each time a row of data that meets the condition is read, sum is increased by 1. Finally, when all the data is read, sum is also increased. Therefore, the total number of items that meet the conditions comes out.

Source

The title is from LeetCode.

Source: LeetCode

Link: leetcode-cn.com/problems/im…

There is another good example, which I recommend you to interpret carefully. Deepen the usage scenarios of this function.

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

Summarize

This is the end of this article about the use of the sum function in MySQL. For more information about the use of the MySQL sum function, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!