Return 0 If Field Is Null in MySQL

Return 0 if field is null in MySQL

Use IFNULL:

IFNULL(expr1, 0)

From the documentation:

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.

How can I set 0 if a query return a null value in MySql?

Use:

SELECT IFNULL(SUM(intervento.IMP_IND_POS_AFF_MIN), 0)

This means IFNULL can be applied to the value returned by SUM in the same way it is applied to a table field.

Replace null with 0 in MySQL

Yes, by using COALESCE.

SELECT COALESCE(null_column, 0) AS null_column FROM whatever;

COALESCE goes through the list of values you give it, and returns the first non-null value.

MySQL return 0 if query is null

You have to do SELECT on IFNULL:

SELECT MAX(`id`) as `count` FROM `stats` LIMIT 1
UNION 
SELECT IFNULL((SELECT COUNT(*) as `count` 
               FROM `stats` 
               WHERE DATE(`time`) >= (NOW() - INTERVAL 1 DAY) LIMIT 1), 0)
UNION 
SELECT IFNULL((SELECT COUNT(*) as `count` 
               FROM `stats` 
               WHERE DATE(`time`) >= (NOW() - INTERVAL 7 DAY) LIMIT 1), 0)
UNION 
SELECT IFNULL((SELECT COUNT(*) as `count` 
               FROM `stats` 
               WHERE DATE(`time`) >= (NOW() - INTERVAL 30 DAY) LIMIT 1), 0)
UNION 
SELECT IFNULL((SELECT DATE_FORMAT(MIN(`time`), '%D of %M, %Y') as `count` 
               FROM `stats` LIMIT 1), 0)

You also have to use 0 instead of '0' and get rid of the parentheses surrounding the first query.

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 |

How to return null as zero in mysql?

The issue is here:

`t2`.`pending` as IFNULL(ending, `0)`

You are checking IFNULL for an alias value of the column, while it should be as following:

IFNULL(`t2`.`pending`, 0) as ending

First evaluate if null - then show 0 and create alias if you want using 'AS' keyword. So, your query will be:

SELECT `t1`.*, IFNULL(`t2`.`pending`, 0) as ending
FROM `tblproducts` t1
LEFT JOIN `cache_invoice` t2 ON `t1`.`id` = `t2`.`product_id`
LEFT JOIN `cache_stock` t3 ON `t1`.`id` = `t3`.`product_id`

---

Related Topics