MySQL SUM函数

Summary: in this tutorial, you will learn how to use the MySQL SUM function to calculate the sum of a set of values or an expression.

Introduction to MySQL SUM function

The SUM function allows you to calculate the sum of a set of values or an expression. The syntax of the SUM function is as follows:

SUM(DISTINCT expression)

How the SUM function works.

• If you use the SUM function in a SELECT statement that returns no matching row, the SUM function returns NULL, not zero.

• The DISTINCT operator allows you to calculate distinct values in the set.

• The SUM function ignores the NULL values in calculation.

MySQL SUM function examples

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

You can calculate the total amount of the order number 10100 by using the SUM function as the following query:

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

Notice that the FORMAT function is used for formatting the returned value of the SUM function.

MySQL SUM with GROUP BY

When combining with the GROUP BY clause, the SUM function calculates the sum for every group specified in the GROUP BY clause.

For example, you can calculate the total amount of each order by 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;

MySQL SUM with HAVING

You can use the SUM function in the HAVING clause to filter the result based on a specific condition. For example, you can calculate the total amount of orders and only select the orders whose total amounts are greater than 60000.

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

MySQL SUM with LIMIT

Suppose you want to calculate the sum of the top 10 most expensive products in the products table, you may come up with the following query:

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

It doesn’t work because the SELECT statement with the SUM function returns one row, and the LIMIT clause constrains the number of rows to return i.e., 3.

To fix this problem, you use a subquery as follows:

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

How it works.

• The subquery selects the top 10 most  expensive products based on the buy prices.

• The outer query calculates the sum of the buy prices of the top 10 expensive products returned from the subquery.

MySQL SUM with NULL

The SUM function returns NULL if there is no matching row. Sometimes, you want the SUM function to return zero instead of NULL. In this case, you can use the COALESCE function. The COALESCE function accepts two argument and returns the second argument if the first argument is NULL, otherwise it returns the first argument; see the following query:

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

MySQL SUM with JOIN

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

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

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

In this tutorial, you have learned how to use the MySQL SUM function to calculate the sum of a set of values.

原文链接：http://outofmemory.cn/mysql/function/mysql-sum