Julien698 Julien698 - 4 years ago 154
SQL Question

How to set the SUM (CASE WHEN) function's return to 0 if there are not values found in MYSQL?

I have a request which count the values of a field in differents cases.

Here is the request :

SELECT SUM(CASE WHEN Reliquat_id = 1 THEN Poids END) AS NbrARRNP,
SUM(CASE WHEN Reliquat_id = 2 THEN Poids END) AS NbrSTNP,
SUM(CASE WHEN Reliquat_id = 3 THEN Nombre END) AS NbrARR,
SUM(CASE WHEN Reliquat_id = 4 THEN Nombre END) AS ST,
SUM(CASE WHEN Reliquat_id = 5 THEN Nombre END) AS NbrCLASS,
SUM(CASE WHEN Reliquat_id = 6 THEN Nombre END) AS NbrINDEX FROM datas WHERE Chantier_id = 4 AND main_id =1;


And sometimes I get a problem if there is no records in a case. The return value is null.


  • For example : if there are no records in the case when Reliquat_id = 2 I get null instead of zero.



I see an other question in StackOverflow which is interesting :

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

I try to use theses functions to my request but I don't understant the syntax to apply in my case.

Have you an idea ?

Thanks

Answer Source

Just add ELSE 0:

SELECT SUM(CASE WHEN Reliquat_id = 1 THEN Poids ELSE 0 END) AS NbrARRNP,
       SUM(CASE WHEN Reliquat_id = 2 THEN Poids ELSE 0 END) AS NbrSTNP,
       SUM(CASE WHEN Reliquat_id = 3 THEN Nombre ELSE 0 END) AS NbrARR,
       SUM(CASE WHEN Reliquat_id = 4 THEN Nombre ELSE 0 ELSE 0 END) AS ST,
       SUM(CASE WHEN Reliquat_id = 5 THEN Nombre END) AS NbrCLASS,
       SUM(CASE WHEN Reliquat_id = 6 THEN Nombre END) ELSE 0 AS NbrINDEX
FROM datas
WHERE Chantier_id = 4 AND main_id = 1;

Note: This will still return a row with all NULL values if no rows at all match the WHERE conditions.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download