Use COALESCE
to avoid that outcome.
SELECT COALESCE[SUM[column],0]
FROM table
WHERE ...
To see it in action, please see this sql fiddle: //www.sqlfiddle.com/#!2/d1542/3/0
More Information:
Given three tables [one with all numbers, one with all nulls, and one with a mixture]:
SQL Fiddle
MySQL 5.5.32 Schema Setup:
CREATE TABLE foo
[
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
val INT
];
INSERT INTO foo [val] VALUES
[null],[1],[null],[2],[null],[3],[null],[4],[null],[5],[null],[6],[null];
CREATE TABLE bar
[
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
val INT
];
INSERT INTO bar [val] VALUES
[1],[2],[3],[4],[5],[6];
CREATE TABLE baz
[
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
val INT
];
INSERT INTO baz [val] VALUES
[null],[null],[null],[null],[null],[null];
Query 1:
SELECT 'foo' as table_name,
'mixed null/non-null' as description,
21 as expected_sum,
COALESCE[SUM[val], 0] as actual_sum
FROM foo
UNION ALL
SELECT 'bar' as table_name,
'all non-null' as description,
21 as expected_sum,
COALESCE[SUM[val], 0] as actual_sum
FROM bar
UNION ALL
SELECT 'baz' as table_name,
'all null' as description,
0 as expected_sum,
COALESCE[SUM[val], 0] as actual_sum
FROM baz
Results:
| TABLE_NAME | DESCRIPTION | EXPECTED_SUM | ACTUAL_SUM |
|------------|---------------------|--------------|------------|
| foo | mixed null/non-null | 21 | 21 |
| bar | all non-null | 21 | 21 |
| baz | all null | 0 | 0 |
Suppose if we are calculating the sum of the values of a column which also have NULL values then MySQL SUM[] function ignores the NULL values and does the sum of the rest of the values. To understand it, consider the following example of table ‘employee’, having following details −
mysql> Select * from Employee; +----+--------+--------+ | ID | Name | Salary | +----+--------+--------+ | 1 | Gaurav | 50000 | | 2 | Rahul | 20000 | | 3 | Advik | 25000 | | 4 | Aarav | 65000 | | 5 | Ram | 20000 | | 6 | Mohan | 30000 | | 7 | Aryan | NULL | | 8 | Vinay | NULL | +----+--------+--------+ 8 rows in set [0.00 sec]
Now, suppose if we want to have the total salary of the employees from above table then while calculating it with SUM[] function, it ignores the NULL values. The following query will produce the required result set −
mysql> Select SUM[Salary] from Employee; +-------------+ | SUM[Salary] | +-------------+ | 210000 | +-------------+ 1 row in set [0.00 sec]
It can also be verified with the help of the following query −
mysql> Select SUM[Salary] from Employee WHERE Salary IS NOT NULL; +-------------+ | SUM[Salary] | +-------------+ | 210000 | +-------------+ 1 row in set [0.00 sec]
Updated on 30-Jul-2019 22:30:21
- Related Questions & Answers
- How MySQL SUM[] function evaluates if it got the column, having character data type, as its argument?
- How MySQL stored function evaluates if it got NULL value while using the dynamic values from a table?
- How MySQL evaluates if I will use an expression within SUM[] function?
- What would be the output of MySQL SUM[] function if a column having no values has been passed as its argument?
- How MySQL evaluates if we use EXISTS operator with the subquery that returns NULL?
- How MySQL evaluates if I store date along with time value in a column having DATE data type?
- What MySQL returns when we use DISTINCT clause with the column having multiple NULL values?
- How MySQL evaluates when I use a conditional expression within SUM[] function?
- What MySQL COUNT[] function returns if there are some NULL values stored in a column also?
- How MySQL SUM[] function evaluates if it is used with SELECT statement that returns no matching rows?
- How can we use MySQL SUM[] function with HAVING clause?
- How can we use MySQL SUM[] function to calculate the sum of only dissimilar values of the column?
- Sum the values in a column in MySQL?
- MySQL query to display only the column values with corresponding column having whitespace
- MySQL update column to NULL for blank values
Summary: in this tutorial, you will learn how to use the MySQL SUM[]
function to calculate the sum of values in a set.
Introduction to the MySQL SUM[]
function
The SUM[]
function is an aggregate function that allows you to calculate the sum of values in a set. The syntax of the SUM[]
function is as follows:
Code language: SQL [Structured Query Language] [sql]
SUM[DISTINCT expression]
Here is how the SUM[]
function works:
- If you use the
SUM[]
function in aSELECT
statement that returns no row, theSUM[]
function returnsNULL
, not zero. - The
DISTINCT
option instructs theSUM[]
function to calculate the sum of only distinct values in a set. - The
SUM[]
function ignores theNULL
values in the calculation.
MySQL SUM[]
function illustration
First,
create a new table named sum_demo
:
Code language: SQL [Structured Query Language] [sql]
CREATE TABLE sum_demo [ n INT ];
Then, insert some rows into the sum_demo
table:
Code language: SQL [Structured Query Language] [sql]
INSERT INTO sum_demo[n] VALUES[1],[1],[2],[NULL],[3];
Third, use the SUM[]
function to calculate the total values in the n
column:
Code language: SQL [Structured Query Language] [sql]
SELECT SUM[n] FROM sum_demo;
As you can see, the SUM[]
function calculates the total of 1, 1, 2, and 3. And it ignores NULL.
Finally, use the SUM[]
with the DISTINCT
option to calculate the total values in the n
column:
Code language: SQL [Structured Query Language] [sql]
SELECT SUM[DISTINCT n] FROM sum_demo;
In this case, the SUM[]
with the DISTINCT
option only calculates the sum of distinct values which are 1, 2 and 3.
MySQL SUM[]
function examples
Let’s take a look at the table orderdetails
in the
sample database.
1] Simple MySQL SUM[]
function example
This example uses the SUM[]
function to get the total number of items of the order details:
Code language: SQL [Structured Query Language] [sql]
SELECT SUM[quantityOrdered] SalesQuantity FROM orderdetails;
2] MySQL SUM[]
function with expression example
The following shows the order line items of the order number 10110:
Code language: SQL [Structured Query Language] [sql]
SELECT orderNumber, quantityOrdered, priceEach FROM orderdetails WHERE orderNumber = 10100;
To calculate the total for the order number 10110, you use the SUM[]
function as follows:
Code language: SQL [Structured Query Language] [sql]
SELECT SUM[quantityOrdered * priceEach] orderTotal FROM orderdetails WHERE orderNumber = 10100;
In this tutorial, the SUM[]
function calculates the total of the following expression of all order line items of the order number 10110:
Code language: SQL [Structured Query Language] [sql]
quantityOrdered * priceEach
3] MySQL SUM[]
with the GROUP BY
clause example
The SUM[]
function is often used with the GROUP BY
clause to calculate the sum for each group.
For example, you can calculate the total amount of each order by using the SUM[]
function with the GROUP BY
clause as shown in the following query:
Code language: SQL [Structured Query Language] [sql]
SELECT orderNumber, SUM[quantityOrdered * priceEach] orderTotal FROM orderdetails GROUP BY orderNumber ORDER BY orderTotal DESC;
In this example:
- The
GROUP BY
clause divides order details into groups grouped by the order number. - The
SUM[]
function calculates the total of each amount of each order.
4] MySQL SUM[]
with HAVING
clause example
You can use the SUM[]
function in the HAVING
clause to filter the group. This example illustrates how to select orders whose order amounts are greater than 60,000
.
SELECT orderNumber, SUM[quantityOrdered * priceEach] orderTotal FROM orderdetails GROUP BY orderNumber HAVING SUM[quantityOrdered * priceEach] > 60000 ORDER BY orderTotal;
Code language: SQL [Structured Query Language] [sql]
5] MySQL SUM[]
with NULL
example
The SUM[]
function returns NULL
if the result set is empty. Sometimes, you may want the SUM[]
function to return zero instead of NULL
.
In this case, you can use the COALESCE[]
function. The COALESCE
function accepts two arguments and returns the second argument if the first argument is NULL
; otherwise, it returns the first argument.
See the following query:
Code language: SQL [Structured Query Language] [sql]
SELECT COALESCE[SUM[quantityOrdered * priceEach], 0] result FROM orderdetails WHERE productCode = 'S1_20';
6] MySQL SUM[]
with join example
See the following orders
and orderdetails
tables:
You can use the SUM[]
function in a SELECT
with JOIN
clause to calculate the sum of values in a table based on a condition specified by the values in another table.
This statement uses the SUM[]
function to
calculate the total amounts of the canceled orders:
Code language: SQL [Structured Query Language] [sql]
SELECT SUM[quantityOrdered * priceEach] cancelled_amount FROM orderdetails INNER JOIN orders USING [orderNumber] WHERE status = 'Cancelled';
7] MySQL SUM IF example
The following statement uses the SUM[]
function to calculate the number of items sold for each order status:
Code language: SQL [Structured Query Language] [sql]
SELECT status, SUM[quantityOrdered] FROM orderdetails INNER JOIN orders USING [orderNumber] GROUP BY status;
If you want to rotate rows to columns, you can use the SUM[]
fnction with CASE
expression. It is kind of SUMIF
logic:
Code language: SQL [Structured Query Language] [sql]
SELECT SUM[CASE WHEN status = 'Shipped' THEN quantityOrdered END] qty_shipped, SUM[CASE WHEN status = 'Resolved' THEN quantityOrdered END] qty_resolved, SUM[CASE WHEN status = 'Cancelled' THEN quantityOrdered END] qty_cancelled, SUM[CASE WHEN status = 'On Hold' THEN quantityOrdered END] qty_on_hold, SUM[CASE WHEN status = 'Disputed' THEN quantityOrdered END] qty_on_disputed, SUM[CASE WHEN status = 'In Process' THEN quantityOrdered END] qty_in_process FROM orderdetails INNER JOIN orders USING [orderNumber];
In this tutorial, you have learned how to use the MySQL SUM[]
function to calculate the sum of a set of values.
Was this tutorial helpful?