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.
| Author |
Topic |
|
jept
Starting Member
14 Posts |
 Posted - 2007-05-21 : 17:46:51
|
| I have written an sql statement thats using sum. The problem that I have is that when the value is zero it returns Null. Is there a way that I could return zero instead of null. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-05-21 : 18:22:49
|
isnull(value, 0)orcoalesce(value, 0)
KH |
 |
|
|
jept
Starting Member
14 Posts |
Posted - 2007-05-22 : 10:59:52
|
quote:
Originally posted by khtan
isnull(value, 0)orcoalesce(value, 0)
KH
This is my statement:Select Sum(isnull(Weight, 0)) as total From Table where Active ='Y'I tried your suggestion and I still get null returned. I just want to know if I am writting the statement right. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-05-22 : 11:12:23
|
If there are no rows matching the criteria in WHERE clause, SUM() will return NULL as well.Try this:Select Coalesce(Sum(Coalesce(Weight, 0)), 0) as total From Table where Active ='Y' Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
jept
Starting Member
14 Posts |
Posted - 2007-05-22 : 11:36:34
|
| the statement you gave me works TY |
|
|
|
subrata4allfriends
Starting Member
24 Posts |
Posted - 2007-05-22 : 13:42:33
|
| You can try in this way also .....SELECT ISNULL(SUM(Weight), 0) AS TOTAL FROM Table WHERE Active ='Y' |
|
|
|
|
|
|
Using Sum in sql statements