How to implement field summation in mysql

In mysql, you can use the SUM() function to implement field summation. This function returns the sum of the specified field values. The syntax is "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 implement field summation.

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

SUM(DISTINCT expression)

SUM()How does the 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 NULL values ​​in calculations.

MySQL SUM() function example

Let’s take a look at the orderdetails table in the sample database (yiibaidb).

You can use the SUM() function to calculate the total amount of the 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 statement, The following results are obtained -

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

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

MySQL SUM and GROUP BY clause

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

For example, the total amount for each order can be calculated using the SUM function with the GROUP BY clause 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 yields 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 by specific conditions. For example, you can calculate the total order quantity and only select orders with a total amount greater than 60000. 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);

Execute the above query statement and get the following results-

mysql> SELECT orderNumber,
       FORMAT(SUM(quantityOrdered * priceEach),2)
FROM orderdetails
GROUP BY orderNumber
HAVING SUM(quantityOrdered * priceEach) > 60000
ORDER BY SUM(quantityOrdered * priceEach);
+-------------+--------------------------------------------+
| orderNumber | FORMAT(SUM(quantityOrdered * priceEach),2) |
+-------------+--------------------------------------------+
|       10310 | 61,234.67                                  |
|       10287 | 61,402.00                                  |
|       10165 | 67,392.85                                  |
+-------------+--------------------------------------------+
3 rows in set

MySQL SUM and LIMIT

Suppose you want to calculateproductsThe sum of the top ten most expensive products in the table, the following query can be asked:

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

Execute the above query statement and get the following results-

mysql> SELECT SUM(buyprice)
FROM products
ORDER BY buyprice DESC
LIMIT 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, 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 -

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

How does the above statement run?

• Subquery selects the top ten most expensive products.
• The outer query calculates the sum of the top 10 products with the highest price 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-

mysql> SELECT COALESCE(SUM(quantityOrdered * priceEach),0)
FROM orderdetails
WHERE productCode = 'S1_212121';
+----------------------------------------------+
| COALESCE(SUM(quantityOrdered * priceEach),0) |
+----------------------------------------------+
| 0.00                                         |
+----------------------------------------------+
1 row in set

MySQL SUM and connection statement

You can use SELECT JOIN in the statement SUMThe function calculates the sum of values ​​in a table based on conditions specified by values ​​in another table.

For example, to calculate the total amount of canceled orders, use the following statement:

SELECT FORMAT(SUM(quantityOrdered * priceEach),2) loss
FROM orderdetails
INNER JOIN orders USING(orderNumber)
WHERE status = 'Cancelled'

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to implement field summation in mysql. For more information, please follow other related articles on the PHP Chinese website!