My Select Sum Query Returns Null. It Should Return 0

My Select SUM query returns null. It should return 0

Try this:

select COALESCE(sum(balance),0) from mytable where customer = 'john' 

This should do the work. The coalesce method should return the 0.

SQL: Sum returns null

You have to apply the IFNULL() higher up, because an empty result set is considered to be null:

SELECT (... 
) + IFNULL((SELECT SUM(`module_attempt`.`score`) ...), 0) AS total_score

How do I get SUM function in MySQL to return '0' if no values are found?

Use COALESCE to avoid that outcome.

SELECT COALESCE(SUM(column),0)
FROM   table
WHERE  ...

To see it in action, please see this sql fiddle: http://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 |

Changing a SUM returned NULL to zero

Put it outside:

SELECT COALESCE(

(  
    SELECT SUM(i.Logged)  
    FROM tbl_Sites s  
    INNER JOIN tbl_Incidents i  
    ON s.Location = i.Location  
    WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -1,0)  
    GROUP BY s.Sites  
), 0)  AS LoggedIncidents

If you are returning multiple rows, change INNER JOIN to LEFT JOIN

SELECT COALESCE(SUM(i.Logged),0)
FROM tbl_Sites s  
LEFT JOIN tbl_Incidents i  
ON s.Location = i.Location  
WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -1,0)  
GROUP BY s.Sites  

By the way, don't put any function or expression inside aggregate functions if it's not warranted, e.g. don't put ISNULL, COALESCE inside of SUM, using function/expression inside aggregation cripples performance, the query will be executed with table scan

How can I make a MySQL SUM query return zero instead of null if there are no records?

This should do the trick:

SELECT COALESCE(SUM(rating),0) AS this_week FROM table_name 
  WHERE UNIX_TIMESTAMP(created_at) >= UNIX_TIMESTAMP() - 604800)

COALESCE is a function that will return the first non NULL value from the list.

How to select sum -or- 0 if no records exist?

How about:

SELECT COALESCE(sum(num), 0) AS val FROM tab WHERE descr LIKE "%greetings%";

The COALESCE function basically says "return the first parameter, unless it's null in which case return the second parameter" - It's quite handy in these scenarios.

Why Sum in database query giving NULL

Just use coalesce [ with 0 as the second argument ] to replace nulls for all month columns, otherwise you can not get true results from aggregation of numeric values :

select sum(coalesce(January,0)+coalesce(February,0) ... )
  from Expense

MySQL SUM returns no rows should return 0

There is no record for product_id = 6706434 in table store2product. As you group by product_id, you get one result row per product_id found with this query. As the product_id is not found, no row is returned.

Simple solution: remove GROUP BY.

SELECT 
  COALESCE(SUM(amount), 0) 
FROM store2product 
WHERE product_id = 6706434;

Now you get one result row in any case.

Why does SUM(...) on an empty recordset return NULL instead of 0?

The ANSI-SQL-Standard defines the result of the SUM of an empty set as NULL. Why they did this, I cannot tell, but at least the behavior should be consistent across all database engines.

Reference: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt on page 126:

b) If AVG, MAX, MIN, or SUM is specified, then

         Case:

         i) If TXA is empty, then the result is the null value.

TXA is the operative resultset from the selected column.

---

Related Topics