Ignore Null in SUM - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Ignore Null in SUM

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-11-12 : 08:36:18

Hi.

Select SUM(Sch1_sheets+Sch2_sheets+Sch3_sheets)
From tbl_ProdSchedule
Where work_order_no = 1339

Columns Sch2_sheets AND Sch3_sheets have data for the given criteria i.e. "work_order_no = 1339"
Column Sch1_sheets has null values for this criteria. How to I get SQL to SUM the totals and assume the NULL values to be zero where it exist?

Currently I get a result of NULL Which I assume is becuase
NULL+3000+3000 = NULL

I want
NULL+3000+3000 = 6000

Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-12 : 08:40:34

SELECT Sum(IsNull(Sch1_sheets, 0) + IsNull(Sch2_sheets, 0) + IsNull(Sch3_sheets, 0))
FROM tbl_ProdSchedule
WHERE work_order_no = 1339

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-11-12 : 08:46:45

Sweet. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources