Mysql addition, result not as expected

Can someone tell me why this isn't working? I'd like to get the sum of two different columns and add them together. The test data is 10 in one column and 10 in the other, totaling 20 开发者_运维问答which is what I'm expecting.

Sum(col1 + col2) as total

---

Probably you have some NULL values in one or both of the columns. The aggregate function SUM ignores NULL values, but the addition operator does not - the value of (1 + NULL) is NULL (not 1 as you might expect). As a result the sum will be lower than expected.

To get the correct sum, you can SUM over each column separately and add the results:

SUM(col1) + SUM(col2) AS total

If one of the columns could be entirely NULL this still won't work. Then you can try this:

SUM(IFNULL(col1, 0)) + IFNULL(col2, 0)) AS total

For example, imagine you have this table:

col1 | col2
-----+----
5    | NULL
5    | 10

Then try these different queries:

SELECT SUM(col1), SUM(col2)        FROM yourtable;    -- Returns 10, 10
SELECT SUM(col1 + col2)            FROM yourtable;    -- Returns 15
SELECT SUM(col1) + SUM(col2)       FROM yourtable;    -- Returns 20 
SELECT SUM(col1 + IFNULL(col2, 0)) FROM yourtable;    -- Returns 20 

Test data:

CREATE TABLE yourtable (col1 INT NOT NULL, col2 INT NULL);
INSERT INTO yourtable (col1, col2) VALUES (5, NULL), (5, 10);