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

Question

Say I have a simple function in MySQL   SELECT SUM Column 1  FROM Table WHERE Column 2    Test    If no entries in Column 2 contain the text  Test  then this function returns NULL  while I would like it to return 0    I m aware that a similar question has been asked a few times here  but I haven t been able to adapt the answers to my purposes  so I d be grateful for some help to get this sorted

User · Answer

Can t get exactly what you are asking but if you are using an aggregate SUM function which implies that you are grouping the table   The query goes for MYSQL like this  Select IFNULL SUM COLUMN1  0  as total from mytable group by condition

User · Answer

Use IFNULL or COALESCE   SELECT IFNULL SUM Column1   0  AS total FROM     SELECT COALESCE SUM Column1   0  AS total FROM      The difference between them is that IFNULL is a MySQL extension that takes two arguments  and COALESCE is a standard SQL function that can take one or more arguments  When you only have two arguments using IFNULL is  slightly faster  though here the difference is insignificant since it is only called once

User · Answer

if sum of column is 0 then display empty select if sum column  gt 0 sum column      from table

User · Answer

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

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

Examples related to mysql

Examples related to function

Examples related to null

Examples related to sum