SQL: sum 3 columns when one column has a null value?

Question

SELECT 
    sum(TotalHoursM)
          + (TotalHoursT)
          + (TotalHoursW)
          + (TotalHoursTH)
          + (TotalHoursF) 
          AS TOTAL
FROM LeaveRequest

SQL Server not tagged ?

Kiquenet
– Kiquenet

2018-03-22 15:39:58 +00:00
Commented Mar 22, 2018 at 15:39 — Kiquenet
– Kiquenet, Commented Mar 22, 2018 at 15:39

Mitchel Sellers · Accepted Answer · 2009-07-06 18:52:05Z

125

If the column has a 0 value, you are fine, my guess is that you have a problem with a Null value, in that case you would need to use IsNull(Column, 0) to ensure it is always 0 at minimum.

answered Jul 6, 2009 at 18:52

Mitchel Sellers

63.3k15 gold badges115 silver badges174 bronze badges

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

6 Comments

ahsteele Over a year ago

Yonita is Michael's answer answered your question mark it as answered by using the check mark.

jameshfisher Over a year ago

"Yonita is Michael's answer answered your question mark it as answered by using the check mark" -- my brain took an age to parse this sentence.

mwfearnley Over a year ago

In MySQL, ISNULL seems to be just a 1-parameter boolean function, but as in omkarv's answer, IFNULL does the job.

jpmc26 Over a year ago

IsNull is not portable, and the question doesn't specify a database. See below for COALESCE, which is portable (if not always optimal in performance).

Luís de Sousa Over a year ago

The question is on SQL, but the IsNull function indicated is not an SQL primitive.

|

Jim Reineri · Accepted Answer · 2019-06-13 11:47:34Z

101

The previous answers using the ISNULL function are correct only for MS Sql Server. The COALESCE function will also work in SQL Server. But will also work in standard SQL database systems. In the given example:

SELECT sum(COALESCE(TotalHoursM,0))
          + COALESCE(TotalHoursT,0)
          + COALESCE(TotalHoursW,0)
          + COALESCE(TotalHoursTH,0)
          + COALESCE(TotalHoursF,0) AS TOTAL FROM LeaveRequest

This is identical to the ISNULL solution with the only difference being the name of the function. Both work in SQL Server but, COALESCE is ANSI standard and ISNULL is not. Also, COALESCE is more flexible. ISNULL will only work with two parameters. If the first parameter is NULL then the value of the second parameter is returned, else the value of the first is returned. COALESCE will take 2 to 'n' (I don't know the limit of 'n') parameters and return the value of the first parameter that is not NULL. When there are only two parameters the effect is the same as ISNULL.

edited Jun 13, 2019 at 11:47

answered Jul 6, 2009 at 19:12

Jim Reineri

2,9825 gold badges31 silver badges32 bronze badges

5 Comments

Darren Over a year ago

Additionally, Oracle SQL does not have ISNULL. Though, in my experience, you may get better results with NVL(): docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm

Mauro Tamm Over a year ago

Just the one i was looking for - postgres doesn't have isnull ether.

George Rappel Over a year ago

Isnull didn't work as expected in MySQL. Coalesce did the job. Thanks.

Luís de Sousa Over a year ago

IsNull is not a SQL primitive. Therefore the other answers are not correct, only this one.

Kiquenet Over a year ago

ISNULL vs COALESCE ?

underscore_d · Accepted Answer · 2020-06-18 15:42:26Z

17

SELECT sum(isnull(TotalHoursM,0)) 
         + isnull(TotalHoursT,0) 
         + isnull(TotalHoursW,0) 
         + isnull(TotalHoursTH,0) 
         + isnull(TotalHoursF,0))
AS TOTAL FROM LeaveRequest

edited Jun 18, 2020 at 15:42

underscore_d

6,8734 gold badges46 silver badges75 bronze badges

answered Jul 6, 2009 at 18:53

gjutras

7721 gold badge4 silver badges14 bronze badges

Comments

omkarv · Accepted Answer · 2015-01-27 09:21:40Z

7

Just for reference, the equivalent statement for MySQL is: IFNull(Column,0).

This statement evaluates as the column value if not null, otherwise it is evaluated as 0.

answered Jan 27, 2015 at 9:21

omkarv

911 silver badge4 bronze badges

Comments

GEOCHET · Accepted Answer · 2009-07-06 18:59:17Z

5

You can use ISNULL:

ISNULL(field, VALUEINCASEOFNULL)

edited Jul 6, 2009 at 18:59

GEOCHET

21.3k15 gold badges78 silver badges99 bronze badges

answered Jul 6, 2009 at 18:52

Colin

10.6k30 silver badges36 bronze badges

Comments

KM. · Accepted Answer · 2009-07-06 19:10:36Z

2

looks like you want to SUM all the columns (I'm not sure where "sum 3 columns" comes from), not just TotalHoursM, so try this:

SELECT 
    SUM(    ISNULL(TotalHoursM  ,0)
          + ISNULL(TotalHoursT  ,0)
          + ISNULL(TotalHoursW  ,0)
          + ISNULL(TotalHoursTH ,0)
          + ISNULL(TotalHoursF  ,0) 
       ) AS TOTAL
    FROM LeaveRequest

answered Jul 6, 2009 at 19:10

KM.

104k34 gold badges183 silver badges214 bronze badges

1 Comment

KM. Over a year ago

thanks for the down vote almost 8 years after answering this! ha ha, this is the exact same answer given by others on this very question. I answered before them, yet they have lots of up votes.

adiga · Accepted Answer · 2017-10-31 09:12:56Z

1

You can also use nvl(Column,0)

edited Oct 31, 2017 at 9:12

adiga

35.4k9 gold badges65 silver badges88 bronze badges

answered Oct 31, 2017 at 9:05

David

111 bronze badge

1 Comment

awd Over a year ago

nvl is specific to oracle pl/sql.

joran · Accepted Answer · 2011-10-27 04:50:25Z

0

I would try this:

select sum (case when TotalHousM is null then 0 else TotalHousM end)
       + (case when TotalHousT is null then 0 else TotalHousT end)
       + (case when TotalHousW is null then 0 else TotalHousW end)
       + (case when TotalHousTH is null then 0 else TotalHousTH end)
       + (case when TotalHousF is null then 0 else TotalHousF end)
       as Total
From LeaveRequest

edited Oct 27, 2011 at 4:50

joran

174k34 gold badges439 silver badges484 bronze badges

answered Oct 27, 2011 at 2:11

Jerome

11 bronze badge

2 Comments

webworm Over a year ago

However I like this solution because it would also be able to take care of those cases where the value is blank and not NULL. You would have to add the LEN() > 0.

Sophia Over a year ago

What is the difference between "being blank" and "NULL"? Thank you.

Mohamed Omer · Accepted Answer · 2016-05-16 19:05:33Z

-3

If you want to avoid the null value use IsNull(Column, 1)

answered May 16, 2016 at 19:05

Mohamed Omer

337 bronze badges

1 Comment

Shiva Over a year ago

IsNull(Column, 1) will give wrong results. It will increase the sum / total by 1 for every null value.

Collectives™ on Stack Overflow

SQL: sum 3 columns when one column has a null value?

9 Answers 9

6 Comments

5 Comments

Comments

Comments

Comments

1 Comment

1 Comment

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

6 Comments

5 Comments

Comments

Comments

Comments

1 Comment

1 Comment

2 Comments

1 Comment

Linked

Related