SUM (isNull,argument) returns 0

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

General SQL Server Forums

New to SQL Server Programming

SUM (isNull,argument) returns 0

Author

Topic

phoenix23
Starting Member

6 Posts

Posted - 2010-07-26 : 05:50:11

hi i am having this problem here

basically I have this SQL Code

Select sum (convert(int,blnIsPostedMO))
From tblMovieQueue
where intTitleId = 10

after executing this statement, it returns null. How do i make it return 0 instead?

Thanks :D

Sachin.Nand

2937 Posts

Posted - 2010-07-26 : 05:57:18

[code]
Select sum (convert(int,IsNull(blnIsPostedMO,0)))
From tblMovieQueue
where intTitleId = 10
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Devart
Posting Yak Master

102 Posts

Posted - 2010-07-26 : 05:57:21

Hi,

Select isnull(sum (convert(int,blnIsPostedMO)),0)
From tblMovieQueue
where intTitleId = 10

Best regards,

Devart Team

Kristen
Test

22859 Posts

Posted - 2010-07-26 : 06:02:06

I prefer Idera's method as it suppresses warnings about NULL data being included, which may upset the application.

But if warnings are not a problem then Devart's may be faster (more likely to use an index if available)

phoenix23
Starting Member

6 Posts

Posted - 2010-07-26 : 06:03:28

Yes! Thanks alot guys for the help , really appreciate it!! got it to work now with Devart answer :D !!!

Sachin.Nand

2937 Posts

Posted - 2010-07-26 : 06:09:26

quote:
Originally posted by Kristen

I prefer Idera's method as it suppresses warnings about NULL data being included, which may upset the application.

But if warnings are not a problem then Devart's may be faster (more likely to use an index if available)

I beg to differ because most probably the column 'blnIsPostedMO' seems to be of a bit type and having an index on that column wont be of much help.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-26 : 06:19:12

The column name "blnIsPostedMO" tells me this column is of type BOOLEAN and this will be enough

SELECT COUNT(*) FROM tblMovieQueue WHERE intTitleID = 10 AND blnIsPostedMO = 1

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2010-07-26 : 06:30:13

"I beg to differ because most probably the column 'blnIsPostedMO' seems to be of a bit type and having an index on that column wont be of much help"

Indeed, but there are other issues at play too ... the fact that IsNull must be applied to every row, rather than just the final SUM total, probably others too if I put my mind to it.

Sachin.Nand

2937 Posts

Posted - 2010-07-26 : 06:39:10

quote:

Indeed, but there are other issues at play too ... the fact that IsNull must be applied to every row, rather than just the final SUM total, probably others too if I put my mind to it.

But again if you use "IsNull(Sum(convert(int,blnIsPostedMO)),0)" you will keep getting those NULL Warnings.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-26 : 08:08:01

It's worth noting that the following are not synonymous in all cases

SELECT Sum(ISNULL(SomeValue,0)) FROM TestSum
SELECT ISNULL(Sum(SomeValue),0) FROM TestSum

The only time SUM will return NULL is if it has no input rows (it's summing an empty resultset) or if all input values are NULL.

In the case where there are no input rows, the first still returns NULL (because there's no rows for the ISNULL to be applied to) while the second returns 0.

Easy to demonstrate:

Create table TestSum (
 SomeValue INT
)

SELECT SUM(SomeValue) FROM TestSum -- null
SELECT Sum(ISNULL(SomeValue,0)) FROM TestSum -- null
SELECT ISNULL(Sum(SomeValue),0) FROM TestSum -- 0

INSERT INTO TestSum (SomeValue) VALUES (NULL)

SELECT SUM(SomeValue) FROM TestSum -- null
SELECT Sum(ISNULL(SomeValue,0)) FROM TestSum -- 0
SELECT ISNULL(Sum(SomeValue),0) FROM TestSum -- 0

So applying the ISNULL inside the SUM can still result in a NULL result.

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-26 : 08:19:08

quote:
Originally posted by Kristen

(more likely to use an index if available)

A function in the select does not influence index usage. Only where the values are compared, grouped or sorted does the existence of the function affect whether or not an index can be used.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-07-26 : 08:26:07

Just to be clear : it won't spoil SQL's chance of choosing a Covering Index?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-26 : 08:35:34

No, because there's no matching, sorting or grouping being done on that column. Functions affect the SARGability of a predicate - the chance of been used for a seek operation.

A covering index is one where all the columns that SQL needs are somewhere in the index. If the blnIsPostedMO column is somewhere in the index, and that index supports the rest of the query, that index is covering, regardless of what happens to the columns in the select clause later (summed, computed, concatenated, etc)

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-07-26 : 08:54:51

"regardless of what happens to the columns in the select clause later"

hadn't thought of it like that, obvious now you have explained it, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources