How to use sum() function in mysql

In mysql, the sum() function is used to calculate the sum of a set of values ​​or expressions. The syntax is "SUM (DISTINCT expression)". The DISTINCT operator allows calculation of different values ​​in the set. The sum() function needs to be used together with the SELECT statement. If the SUM() function is used in a SELECT statement that does not return matching rows, the SUM() function will return NULL instead of 0; the SUM() function will ignore the columns when calculating. Rows with NULL value.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

mysql sum() function

The SUM() function is used to calculate the sum of a set of values ​​or expressions. The SUM() function The syntax is as follows:

SUM(DISTINCT expression)

##ParameterDescriptionRequired. Field or formula

expression

#How does the SUM() function work?

• If the SUM function is used in a SELECT statement that does not return matching rows, the SUM function returns NULL instead of 0.

• The DISTINCT operator allows calculation of distinct values ​​in a collection.

• The SUM() function ignores rows with NULL column values ​​when calculating.

Usage example of sum() function

In order to facilitate understanding, first create a student score table tb_students_score, students The data content of the score table is as follows

mysql> SELECT * FROM tb_students_score;

Use the sum() function to calculate the total score of the students’ scores in the tb_students_score table

mysql> SELECT SUM(student_score) AS score_sum FROM tb_students_score;

As you can see from the query results, the SUM() function returns the sum of all the student's grades as 942.

Extended knowledge:

MySQL SUM and GROUP BY clause

When to use GROUP BY When clauses are combined, the SUM() function calculates the sum for each grouping specified in the GROUP BY clause. For example, you can use the SUM function with a GROUP BY clause to calculate the total amount of each order, as follows:

SELECT orderNumber,
       FORMAT(SUM(quantityOrdered * priceEach),2) total
FROM orderdetails
GROUP BY orderNumber
ORDER BY SUM(quantityOrdered * priceEach) DESC;

Execute the above query statement and get the following results-

+-------------+-----------+
| orderNumber | total     |
+-------------+-----------+
|       10165 | 67,392.85 |
|       10287 | 61,402.00 |
|       10310 | 61,234.67 |
|       10212 | 59,830.55 |
*** 此处省略了一大波数据 *****
|       10116 | 1,627.56  |
|       10158 | 1,491.38  |
|       10144 | 1,128.20  |
|       10408 | 615.45    |
+-------------+-----------+
327 rows in set

MySQL SUM and HAVING

You can use the HAVING clause in the SUM function to filter results based on specific criteria. For example, you can calculate the total order quantity and only select orders with a total amount greater than 60,000. The following query statement-

SELECT orderNumber,
       FORMAT(SUM(quantityOrdered * priceEach),2)
FROM orderdetails
GROUP BY orderNumber
HAVING SUM(quantityOrdered * priceEach) > 60000
ORDER BY SUM(quantityOrdered * priceEach);

executes the above query statement and gets the following results-

MySQL SUM and LIMIT

Suppose you want to calculate the sum of the top ten most expensive products in the products table, you can ask the following query:

SELECT SUM(buyprice)
FROM products
ORDER BY buyprice DESC
LIMIT 10;

Execute the above query statement and get the following results-

It does not work because the SELECT statement with the SUM function returns only one row and the LIMIT clause constraint on the number of rows to be returned is invalid. To solve this problem, please use the following subquery:

SELECT FORMAT(SUM(buyprice),2) FROM
(SELECT buyprice
FROM products
ORDER BY buyprice DESC
LIMIT 10) price;

Execute the above query statement and get the following results -

How does the above statement run? The subquery selects the top ten most expensive products. The outer query calculates the sum of the top 10 highest priced products returned from the subquery.

MySQL SUM and NULL

If there are no matching rows, the SUM function returns a NULL value. Sometimes you want the SUM function to return 0 instead of NULL. In this case, you can use the COALESCE function. The COALESCE function accepts two parameters. If the first parameter is NULL, the second parameter is returned, otherwise the first parameter is returned; refer to the following query statement:

SELECT COALESCE(SUM(quantityOrdered * priceEach),0)
FROM orderdetails
WHERE productCode = 'S1_212121';

Execute the above query statement and get the following results -

[Related recommendations:

mysql video tutorial]

The above is the detailed content of How to use sum() function in mysql. For more information, please follow other related articles on the PHP Chinese website!