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 theSUMfunction is used in aSELECTstatement that does not return matching rows, theSUMfunction returnsNULLinstead of0. The
• DISTINCToperator allows calculation of distinct values in a collection. The
• SUMfunction ignoresNULLvalues in calculations.

MySQL SUM() function example

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

You can use theSUM()function to calculate the total amount of the order number10100, 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 theFORMAT()function is used to format the return value of theSUM()function.

MySQL SUM and GROUP BY clause

When combined with theGROUP BYclause, theSUM()function Calculates the sum of each group specified in theGROUP BYclause.

For example, the total amount for each order can be calculated using theSUMfunction with theGROUP BYclause 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 theHAVINGclause in theSUMfunction to Filter results by specific conditions. For example, you can calculate the total order quantity and only select orders with a total amount greater than60000. 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 theSELECTstatement with theSUMfunction returns only one row, theLIMITclause 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 top10products with the highest price returned from the subquery.

MySQL SUM and NULL

If there are no matching rows, theSUMfunction returns aNULLvalue. Sometimes you want theSUMfunction to return0instead ofNULL. In this case, you can use theCOALESCEfunction. TheCOALESCEfunction accepts two parameters. If the first parameter isNULL, 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 useSELECTJOINin the statementSUMThe 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!