Sum on multiple columns with nullable values

Question

I have to edit a stored procedure who has to return the sums of three columns having nullable values. If there is a null value, I need to cast it to 0

Here is a screenshot of data :

enter image description here

And here is the originial request using the first column only :

SELECT SUM(reglProj.Montant) /* SUM of 'Montant', 'FraisMagasing', 'FraisVendeur' instead */ AS SommeReglement
FROM Projet.LigneEcheancierProjet ligne
INNER JOIN Projet.ReglementProjetEcheance reglProj ON reglProj.LigneEcheancierProjetId = ligne.LigneEcheancierProjetId
....

Do you have some best practices using the sum and case conditions in T-SQL ?

where is your other 2 column in sql query , which you want to cast — Ajay2707
– Ajay2707, Commented Jul 21, 2015 at 13:45
Why would you want to do that? SUM ignores null values (basically treat them as 0) by default... — Zohar Peled
– Zohar Peled, Commented Jul 21, 2015 at 13:48
Sum ignores. That's true. But he wants to add values and when you add NULL then the result becomes NULL — Dmitrij Kultasev
– Dmitrij Kultasev, Commented Jul 21, 2015 at 13:57

Dmitrij Kultasev · Accepted Answer · 2015-07-21 13:51:09Z

3

--ANSI standard
    SELECT SUM(COALESCE(col1,0)) + SUM(COALESCE(col2,0)) + SUM(COALESCE(col3,0))

--SQL Server Style    
    SELECT SUM(ISNULL(col1,0)) + SUM(ISNULL(col2,0)) + SUM(ISNULL(col3,0))

--The one wthout functions. It will work the same as previous OR FASTER.    
    SELECT SUM(CASE WHEN col1 IS NULL THEN 0 ELSE col1 END) + SUM(CASE WHEN col2 IS NULL THEN 0 ELSE col2 END) + SUM(CASE WHEN col3 IS NULL THEN 0 ELSE col3 END)

Choose one for yourself.

OR you might need following (if you want to add sums by row):

--ANSI standard
    SELECT SUM(COALESCE(col1,0) +COALESCE(col2,0) + COALESCE(col3,0))

--SQL Server Style    
    SELECT SUM(ISNULL(col1,0)+ ISNULL(col2,0) + ISNULL(col3,0))

--The one wthout functions. It will work the same as previous OR FASTER.    
    SELECT SUM(CASE WHEN col1 IS NULL THEN 0 ELSE col1 END + CASE WHEN col2 IS NULL THEN 0 ELSE col2 END + CASE WHEN col3 IS NULL THEN 0 ELSE col3 END)

edited Jul 21, 2015 at 13:51

answered Jul 21, 2015 at 13:45

Dmitrij Kultasev

5,8658 gold badges53 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Xavier W. Over a year ago

Thx for the result. I edit my problem because I need to do the sum of my three columns (in the same table) : 'Montant', 'FraisMagasing', 'FraisVendeur'

Zohar Peled · Accepted Answer · 2015-07-21 13:58:12Z

In Sql Server, (and probably in most if not all relational databases) the SUM Aggregation function ignores null values by default, so there really is no need to use coalesce or isnull inside it.

If you want the sum of all 3 columns for every single row, then you need to use isnull:

SELECT ISNULL(reglProj.Montant,0) + 
       ISNULL(reglProj.FraisMagasing ,0) + 
       ISNULL(reglProj.FraisVendeur,0)
FROM Projet.LigneEcheancierProjet ligne
INNER JOIN Projet.ReglementProjetEcheance reglProj 
      ON reglProj.LigneEcheancierProjetId = ligne.LigneEcheancierProjetId

If you need the aggregated sum of all 3 columns you can simply do it like this:

SELECT ISNULL(SUM(reglProj.Montant), 0) + 
       ISNULL(SUM(reglProj.FraisMagasing), 0) + 
       ISNULL(SUM(reglProj.FraisVendeur), 0)
FROM Projet.LigneEcheancierProjet ligne
INNER JOIN Projet.ReglementProjetEcheance reglProj 
      ON reglProj.LigneEcheancierProjetId = ligne.LigneEcheancierProjetId

Haseeb Asif · Accepted Answer · 2015-07-21 13:56:29Z

0

It seems you are looking for ISNULL actually

SELECT SUM( ISNULL(reglProj.Montant,0) + ISNULL(FraisMagasing,0)+ ISNULL(FraisVendeur,0))    AS SommeReglement
FROM Projet.LigneEcheancierProjet ligne
INNER JOIN Projet.ReglementProjetEcheance reglProj ON reglProj.LigneEcheancierProjetId = ligne.LigneEcheancierProjetId

edited Jul 21, 2015 at 13:56

answered Jul 21, 2015 at 13:48

Haseeb Asif

1,8062 gold badges25 silver badges44 bronze badges

Collectives™ on Stack Overflow

Sum on multiple columns with nullable values

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related