Show a value other then NULL  
Author Message
Somar





PostPosted: Tue Nov 18 11:27:18 CST 2003 Top

SQL Server Developer >> Show a value other then NULL

Hi All,
I want to show a value of '0' instead of seeing a value of Null.

example:

SELECT (SUM(Field1) + SUM(Field2)) As [SumOfFields]
From Table1

I may get a result similar to
[SumOfFields]
---------------
3
Null
5
2
Null
35
etc...

I've tried using a CASE statement such as;

CASE When ((SUM(Field1) + SUM(Field2)) As [SumOfFields]) = Null Then 0 Else
[SumOfFields] End

I have tried a couple of permutations but I am just not getting it.

Any help would be appreciated.
John.

SQL Server48  
 
 
Trey





PostPosted: Tue Nov 18 11:27:18 CST 2003 Top

SQL Server Developer >> Show a value other then NULL Try

SELECT SUM( ISNULL(FIELD1, 0) + ISNULL(FIELD2, 0) ) AS SumOfFields





> Hi All,
> I want to show a value of '0' instead of seeing a value of Null.
>
> example:
>
> SELECT (SUM(Field1) + SUM(Field2)) As [SumOfFields]
> From Table1
>
> I may get a result similar to
> [SumOfFields]
> ---------------
> 3
> Null
> 5
> 2
> Null
> 35
> etc...
>
> I've tried using a CASE statement such as;
>
> CASE When ((SUM(Field1) + SUM(Field2)) As [SumOfFields]) = Null Then 0
Else
> [SumOfFields] End
>
> I have tried a couple of permutations but I am just not getting it.
>
> Any help would be appreciated.
> John.
>
>


 
 
Adam





PostPosted: Tue Nov 18 11:28:17 CST 2003 Top

SQL Server Developer >> Show a value other then NULL Try:

COALESCE((SUM(Field1) + SUM(Field2)), 0) As [SumOfFields]

Or, if you prefer the CASE approach:

CASE WHEN ((SUM(Field1) + SUM(Field2)) IS NULL THEN 0 ELSE ((SUM(Field1) +
SUM(Field2)) END AS [SumOfFields]

Remember, NULL != NULL. Use IS NULL to compare against NULL values.




> Hi All,
> I want to show a value of '0' instead of seeing a value of Null.
>
> example:
>
> SELECT (SUM(Field1) + SUM(Field2)) As [SumOfFields]
> From Table1
>
> I may get a result similar to
> [SumOfFields]
> ---------------
> 3
> Null
> 5
> 2
> Null
> 35
> etc...
>
> I've tried using a CASE statement such as;
>
> CASE When ((SUM(Field1) + SUM(Field2)) As [SumOfFields]) = Null Then 0
Else
> [SumOfFields] End
>
> I have tried a couple of permutations but I am just not getting it.
>
> Any help would be appreciated.
> John.
>
>


 
 
David





PostPosted: Tue Nov 18 11:29:06 CST 2003 Top

SQL Server Developer >> Show a value other then NULL Try either

SELECT COALESCE(SUM(col1),0) + COALESCE(SUM(col2),0)
FROM Table1

or

SELECT COALESCE(SUM(col1)+SUM(col2),0)
FROM Table1

depending on the result you want.

--
David Portas
------------
Please reply only to the newsgroup
--


 
 
John





PostPosted: Tue Nov 18 11:41:18 CST 2003 Top

SQL Server Developer >> Show a value other then NULL Thank you all very much. I keep forgetting about that great function
Coalesce :)



> Try either
>
> SELECT COALESCE(SUM(col1),0) + COALESCE(SUM(col2),0)
> FROM Table1
>
> or
>
> SELECT COALESCE(SUM(col1)+SUM(col2),0)
> FROM Table1
>
> depending on the result you want.
>
> --
> David Portas
> ------------
> Please reply only to the newsgroup
> --
>
>