CODEWITHSUNDEEP
sqlsql-server
Xavier W.
Xavier W.

Reputation: 1360

Sum on multiple columns with nullable values

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 ?

Upvotes: 0

Views: 1653

Answers (3)

Zohar Peled
Zohar Peled

Reputation: 82474

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

Upvotes: 1

Haseeb Asif
Haseeb Asif

Reputation: 1786

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

Upvotes: 0

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

--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)

Upvotes: 3

Related Questions