NULL value add with a NUMBER help please

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)

NULL value add with a NUMBER help please

Author

Topic

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-01-11 : 13:39:42

Hello friends,
I was trying to SUM a NULL value with a number, it shows zero (0) as a result. Can you please help me how to add a NULL with a number???
Ex: Null + 34 must be equal to 34
In my case Null + 34 = 0, this is wrong....
PLEASE HELP.

jENY.

The stupid question is the question you don't ask.
www.single123.com

amachanic
SQL Server MVP

169 Posts

Posted - 2005-01-11 : 13:44:52

Use COALESCE?

COALESCE(YourNullableCol, 0) + 34

Kristen
Test

22859 Posts

Posted - 2005-01-11 : 13:56:03

Do you mean

SELECT SUM(MyColumn) FROM MyTable?

or

SELECT 34 + NULL

because the second one should give you NULL and not Zero

In the first case you could do:

SELECT SUM(MyColumn) FROM MyTable WHERE MyColumn IS NOT NULL

or

SELECT SUM(COALESCE(MyColumn), 0) FROM MyTable

and for the Second Case you should use Adam's suggestion

Kristen

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-01-11 : 14:06:52

Yes, it worked.
Do you guys know what ROUND function in SQL does?
I took an Access query out and created a view in sql, but I don't really know what ROUND function in sql does?
JennyPretty.

The stupid question is the question you don't ask.
www.single123.com

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-01-11 : 14:30:19

>>>Do you guys know what ROUND function in SQL does?

select ROUND
from BooksOnline

>>>SELECT SUM(COALESCE(MyColumn), 0) FROM MyTable

A minor point but (yes, I am "retentive") you don't need to coalesce the value since the aggregate will ignore Null values.

HTH

=================================================================

Scriptures, n. The sacred books of our holy religion, as distinguished from the false and profane writings on which all other faiths are based.
-Ambrose Bierce, writer (1842-1914) [The Devil's Dictionary]

Kristen
Test

22859 Posts

Posted - 2005-01-12 : 10:09:32

"A minor point but (yes, I am "retentive") you don't need to coalesce the value since the aggregate will ignore Null values."

If you don't use COALESCE (or kill the NULLs using a suitable WHERE clause) you'll get one of those pesky ANSI warnings that always mucks up ADO - won't you?

Kristen

amachanic
SQL Server MVP

169 Posts

Posted - 2005-01-12 : 10:30:30

Well, there's always:

set ansi_warnings off

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-12 : 10:39:53

if all the values are NULL, you'll get Null when you might want zero. though in that case, you should put the COALESCE() around the SUM, not inside it, I suppose.

- Jeff

Kristen
Test

22859 Posts

Posted - 2005-01-13 : 00:23:34

"set ansi_warnings off"

Too scary for me

- might hide some other error in my code / data.

I, personally, am happier with

SELECT SUM(COALESCE(MyColumn), 0) FROM MyTable
or
SELECT SUM(MyColumn) FROM MyTable WHERE MyColumn IS NOT NULL

so that I can code my intentions, rather than have SQL Servers interpretation of my intentions!

Kristen

amachanic
SQL Server MVP

169 Posts

Posted - 2005-01-13 : 08:38:32

It was intended to be a (not very funny) joke....

Agreed on not relying on implicit actions!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources