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 |
|
nodje
Starting Member
6 Posts |
 Posted - 2004-07-22 : 04:46:00
|
| Hi,i'm actually building report requests that make a lot of SUM.I sometimes have to return the addition of these SUM. It works well with the + operator except if one of the two amount is NULL.the + operator seems return NULL if one of the argument is NULL.My question is how can I replace a NULL by a zero in the returned rows?Regards |
|
|
JasonGoff
Posting Yak Master
158 Posts |
Posted - 2004-07-22 : 04:47:39
|
| Look at books online for ISNULL() or COALESCE(). If you want an example, post your SQL statement here. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-22 : 05:10:11
|
| You might also want to loopk at excluding the NULL values from the SUM in the first place - that will have the added benefit of surpressing any "Ansi Warning Messages" which can spoil your day when they get caught by ADO ...So two routes:SELECT COALESCE(SUM(MyCol), 0) FROM MyTableOr, my prefered one, SELECT SUM(MyCol) FROM MyTable WHERE MyCol IS NOT NULLOr if that is not easily achievable I supposeSELECT SUM(COALESCE(MyCol, 0)) FROM MyTablewould have the benefit of surpressing the Ansi Warnings. However, do NOT use this technique with AVERAGE() without thinking about the consequences!!!Kristen |
|
|
|
nodje
Starting Member
6 Posts |
Posted - 2004-07-22 : 07:04:28
|
| Thanks guys, that's exactly what I was looking for!A very actif forum indeed ...Cheers |
|
|
|
|
|
|
the + operator