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 |
|
leonvh
Starting Member
5 Posts |
 Posted - 2004-05-10 : 11:34:07
|
| AS returns the value NULL when performing a sum over zeros (0). Can this be changed so that the value zero (0) is returned?Leon |
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-05-10 : 11:39:03
|
| Use the ISNULL function on the column or variables you are summming.Raymond |
 |
|
|
leonvh
Starting Member
5 Posts |
Posted - 2004-05-11 : 02:56:23
|
| You mean the IsEmpty function? If I use that to test on empty and set to 0, all values will be set to 0 which is incorrect: In my opinion, SUM (0) (One or more zeros) should give 0 and not NULL or empty!Leon |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-11 : 08:02:32
|
| He meant ISNULL just like he said. :) SUM(ISNULL(column,0). This changes the NULL fields to 0, then SUMs all fields.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
leonvh
Starting Member
5 Posts |
Posted - 2004-05-11 : 08:15:37
|
| Thanks, but that is exactly what I don't want: If all detail values are NULL, then the result should also be NULL. Only in the case that there are zero detail values (And maybe NULLs as well) the result should be 0. In short I expect the following:SUM (0,0,0) = 0SUM (0,NULL,0) = 0SUM (NULL, NULL, NULL) = NULLNow AS returns NULL for all situations above! |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-11 : 08:32:42
|
| Why? I can't imagine a world this would make sense in.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
leonvh
Starting Member
5 Posts |
Posted - 2004-05-11 : 09:11:43
|
| You could start a discussion about the second situation (SUM (0,NULL,0) = 0), but the sum of all zeros should return zero! If my measure is PRODUCTS_SOLD_QTY, then with '0' I know that we have not sold anything, but NULL could also mean that that product is not available in the store that I selected. Quite a difference! |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-11 : 10:50:05
|
| ???? That's just bad design. You need to have a table that says how much is available in a store or what stores a certain product is available in.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
Sum of zeros (0)