Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to achieve field summation in mysql. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

In mysql, you can use the SUM () function to sum the fields, which returns the sum of the specified field values, with the syntax "SELECT SUM (DISTINCT expression) FROM table name [WHERE clause];)".

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

In mysql, you can use the SUM () function to achieve field summation.

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

SUM (DISTINCT expression)

How does the SUM () function work?

If you use the SUM function in a SELECT statement that does not return a matching row, the SUM function returns NULL instead of 0.

The DISTINCT operator allows you to evaluate different values in the collection.

The SUM function ignores the null value in the calculation.

Example of the MySQL SUM () function

Let's look at the orderdetails table in the sample database (yiibaidb).

You can use the SUM () function to calculate the total amount of order number 10100, as shown in the following query:

SELECT FORMAT (SUM (quantityOrdered * priceEach), 2) total FROM orderdetails WHERE orderNumber = 10100

Execute the above query and get the following results-

Mysql > SELECT FORMAT (SUM (quantityOrdered * priceEach), 2) total FROM orderdetails WHERE orderNumber = 10100 row in set + | total | +-+ | 10223.83 | +-+ 1 row in set

Note that the FORMAT () function is used to format the return value of the SUM () function.

MySQL SUM and GROUP BY clauses

When combined with the GROUP BY clause, the SUM () function calculates the sum of each packet specified in the GROUP BY clause.

For example, you can use the SUM function with a GROUP BY clause to calculate the total amount for each order, as follows:

SELECT orderNumber, FORMAT (SUM (quantityOrdered * priceEach), 2) totalFROM orderdetailsGROUP BY orderNumberORDER BY SUM (quantityOrdered * priceEach) DESC

Execute the above query and get the following results-

+-+ | orderNumber | total | +-+-+ | 10165 | 67392.85 | | 10287 | 61402.00 | 10310 | 61234.67 | | 10212 | 59830.55 | * * A large wave of data is omitted here * | 10116 | | 1627.56 | | 10158 | 1491.38 | | 10144 | 1128.20 | | 10408 | 615.45 | +-+-+ 327 rows in setMySQL SUM and HAVING |

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

SELECT orderNumber, FORMAT (SUM (quantityOrdered * priceEach), 2) FROM orderdetailsGROUP BY orderNumberHAVING SUM (quantityOrdered * priceEach) > 60000ORDER BY SUM (quantityOrdered * priceEach)

Execute the above query and get the following results-

Mysql > SELECT orderNumber, FORMAT (SUM (quantityOrdered * priceEach), 2) FROM orderdetailsGROUP BY orderNumberHAVING SUM (quantityOrdered * priceEach) > 60000ORDER BY SUM (quantityOrdered * priceEach) +-+-+ | orderNumber | FORMAT (SUM (quantityOrdered * priceEach) 2) | +-+-- + | 10310 | 61234.67 | | 10287 | 61402.00 | | 10165 | 67392.85 | | +-+-+ 3 rows in setMySQL SUM and LIMIT |

Assuming that you want to calculate the sum of the top ten most expensive products in the products table, you can make the following query:

SELECT SUM (buyprice) FROM productsORDER BY buyprice DESCLIMIT 10

Execute the above query and get the following results-

Mysql > SELECT SUM (buyprice) FROM productsORDER BY buyprice DESCLIMIT 10 + | SUM (buyprice) | +-+ | 5983.47 | +-+ 1 row in set

It does not work because the SELECT statement with the SUM function returns only one row, and the LIMIT clause constraint has an invalid number of rows to return.

To resolve this problem, use the following subquery:

SELECT FORMAT (SUM (buyprice), 2) FROM (SELECT buypriceFROM productsORDER BY buyprice DESCLIMIT 10) price

Execute the above query and get the following results-

+-+ | FORMAT (SUM (buyprice), 2) | +-+ | 958.71 | +-+ 1 row in set

How does the above statement work?

The subquery selects the top ten products with the highest price.

The external query calculates the sum of the top 10 most expensive products returned from the subquery.

MySQL SUM and NULL

If there are no matching rows, the SUM function returns null. 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 orderdetailsWHERE productCode = 'S114212121'

Execute the above query and get the following results-

Mysql > SELECT COALESCE (SUM (quantityOrdered * priceEach), 0) FROM orderdetailsWHERE productCode = 'S114212121' +-+ | COALESCE (SUM (quantityOrdered * priceEach) 0) | +-+ | 0.00 | +-+ 1 row in setMySQL SUM and connection statement

You can use the SUM function in the SELECT JOIN statement to calculate the sum of the values in the table based on the conditions specified by the values in another table.

For example, to calculate the sum of the cancelled order amount, use the following statement:

SELECT FORMAT (SUM (quantityOrdered * priceEach), 2) lossFROM orderdetailsINNER JOIN orders USING (orderNumber) WHERE status = 'Cancelled' on "how to achieve field summation in mysql" this article ends here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.