SQL - Adding two select statements, handling NULL

BobBarker_99
BobBarker_99 used Ask the Experts™
on
Here is my problem.  

I have a query like: SELECT (SELECT SUM (fld1) FROM tbl1 + SELECT SUM9fld1 FROM tbl2)

This works fine, except when 1 (or both) of the SELECT SUM statements returns NULL.  How can I make it return a 0 instead of a NULL so it doesnt mess up my calculation?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Just to clarify, the problem is that if the first select returns NULL and the second returns 500
the result is still NULL
Top Expert 2010
Commented:
SELECT (SELECT SUM (COALESCE(fld1,0)) FROM tbl1 + SELECT SUM(COALESCE(fld1,0)) FROM tbl2)

Author

Commented:
select SUM(COALESCE(fldAmount, 0)) FROM tblPaymentOut WHERE fldContactRef = @ContactRef

Is still returning NULL
Commented:
When a select statement does not find any valid rows - the statement returns a null value.
You have to account for that situation by wrapping the select with COALESCE, as in:
SELECT (COALESCE(SELECT SUM(fld1) FROM tbl1, 0));
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Small Change in mathewspatrick's comment:
SELECT (SELECT SUM(COALESCE(fld1,0)) FROM tbl1) 
+ (SELECT SUM(COALESCE(fld1,0)) FROM tbl2) as sum_total

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial