Sum and null values (casing) - 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

Transact-SQL (2000)

Sum and null values (casing)

Author

Topic

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-05-06 : 06:33:36

on my query there is a left join and with this there comes null values in some columns.
But i want to change null values to 0
i tries this bit it give me the wrong result

case when sum(Money) is null then 0 end

the query above sets all numeric values to null and all null values to 0 why??
how can i set null values to 0

MS BLESS US

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-06 : 06:37:50

Select IsNull(Money,0) From Tblname

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-05-06 : 06:47:34

nice
thx

MS BLESS US

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-05-06 : 07:03:08

count(IsNull(Word,0))
ok that works
but count result comes "1" when there is no result
if there are 5 words it counts normally but if there is no word it counts it as 1 why ?
my query is like this.

Select t1.ID,t1.UserName,t1.corpName,
count(IsNull(t2.Word,0))wordcount,sum(isNull(money,0)) totalmoney
,sum(IsNull(money2,0))money2 from tbl_a t1
left join tbl_b t2 on t2.ID = t1.ID
group by t1.ID,t1.UserName,t1.corpName

on tbl_a there are corporations and informations about them
on tbl_b there are words that bought by corporatoins
and my query gets a short information about them (wordcount,totalmoney...like that)
but why count comes as 1?
when a corp bought one word it counts as 1 ok that is rgiht
but when there is no it counts as 1 too.

MS BLESS US

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-06 : 07:17:03

can you post the same data from the table.. since i dont think you require to use isnull function for the counting the records.

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-05-06 : 07:43:03

tbl_a
ID Corp
1 jack
2 Joe
3 jessy

tbl_b
skbID Word Corp Money ID
18 AMD jack 3 1
19 ATI jack 3 1
20 Intel joe 1 2

and result will count how many words each copr have (but all corp. so i used left join)
total money ( i get this correctly(
my problem is only count

MS BLESS US

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-06 : 08:02:03

change this

count(IsNull(t2.Word,0))wordcount

count(t2.Word)wordcount

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-05-06 : 08:09:39

oh damn
yeah right
ok thx again

MS BLESS US

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources