Sum of NULL problem with result, if the value of the data is null

Question

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (1 votes)

0

I need to bring the sum of 2 data:

dato1 = 1
dato2 = 2
totalsuma = 3

Currently I do it like this:

 SET  totalsuma = (SELECT SUM(dato1+dato2) from tabla WHERE  CODIGO  = codigo_new);

up there all right the totalsum variable takes me the value 3, the problem I have when some of the data has zero value, eg:

dato1 = 1
dato2 = null
totalsuma = null

When I have some of the data in null , the expected task is simply not performed.

Someone can help me in my stored procedure , thank you very much.

sql stored-procedure

asked by Francisco Acevedo 27.06.2018 в 00:36

source

3 answers

1

Possible solutions:

MySQL

The MySQL IFNULL () function returns a value in case the expression is null: IFNULL(dato,0)

SQL Server

the SQL SERVER ISNULL () function also allows you to return an alternative value in case the variable is null:% ISNULL(dato,0)

Oracle

The function of ORACLE NVL () gives us the same result as the previous ones:
NVL(dato, 0)

In your specific case you would need to do something like this:

SET  totalsuma = (SELECT SUM(ISNULL(dato1,0)+ISNULL(dato2,0)) 
from tabla WHERE  CODIGO  = codigo_new);

answered by 27.06.2018 в 00:44

1

To complement, it could also be done with COALESCE , as is done with IFNULL .

COALESCE is part of the TSQL standard.

SET totalsuma = (SELECT SUM(COALESCE(dato1, 0) + COALESCE(dato2,0)) 
                from tabla 
                WHERE CODIGO = codigo_new);

answered by 27.06.2018 в 16:07

score 1 · Accepted Answer

1

Try using the ifnull

feature

SET totalsuma = (SELECT SUM(ifnull( dato1, 0 ) + ifnull(dato2,0)) 
                from tabla 
                WHERE CODIGO = codigo_new);

answered by 27.06.2018 / 00:41

source