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

Question

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

you can add the else in case like SUM(CASE WHEN Reliquat_id = 1 THEN Poids ELSE 0 END) — Vipin Jain, Dec 30 '15 at 12:43

score 3 · Accepted Answer · edited Dec 30 '15 at 14:03

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  END) AS ST,
       SUM(CASE WHEN Reliquat_id = 5 THEN Nombre ELSE 0 END) AS NbrCLASS,
       SUM(CASE WHEN Reliquat_id = 6 THEN Nombre ELSE 0 END)  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.

score 3 · Answer 2 · edited Jun 20 '20 at 09:12

3

Use IFNULL() or COALESCE() function

SQL NULL Functions-W3 Schools Ref

IFNULL(Poids,0) or COALESCE(Poids,0)

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 30 '15 at 12:44

rupesh_padhye

1,355
2
13
25

The Query is working but there is a problem in the return. I get 5.92 | null | 0 | null | null | null instead of 5.92 | null | null | null | null in my first request without modifications. And with else I get 5.92 | 0 | 0 | 0 | 0 – Julien698 Dec 30 '15 at 13:13

score 1 · Answer 3 · edited Dec 30 '15 at 15:00

1

Please try this

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

edited Dec 30 '15 at 15:00

Julien698

676
3
10
27

answered Dec 30 '15 at 12:53

Rita Chavda

191
2
16

I've just tried, it says me : " Incorrect parameter count in the call to native function 'ISNULL' " – Julien698 Dec 30 '15 at 13:03
Why put ISNULL if the ELSE still there ? – Julien698 Dec 30 '15 at 13:07
'Else' work if record found and Reliquat_id <> 1 ,and ISNULL work when record is not found – Rita Chavda Dec 30 '15 at 13:22
ISNULL is for sql,you have to use IFNULL – Rita Chavda Dec 30 '15 at 13:22
Finally it works too with the ELSE and without the ELSE thanks for your solution – Julien698 Dec 30 '15 at 13:27

score 0 · Answer 4 · answered Dec 30 '15 at 12:57

Use Else 0 for cases (Reliquat_id = 5 and Reliquat_id = 6) as well as you doing for other cases.Try this :

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 END) AS ST,
       SUM(CASE WHEN Reliquat_id = 5 THEN Nombre ELSE 0 END) AS NbrCLASS,
       SUM(CASE WHEN Reliquat_id = 6 THEN Nombre ELSE 0 END) AS NbrINDEX 
       from

score 0 · Answer 5 · answered Dec 30 '15 at 13:51

Finally here's the last request :

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

I find that's the best request to fix that problem because if there are no records in the database, "ELSE 0" doesn't work and return 0. Now with IFNULL even if there are no records, it always returns 0.

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

5 Answers5