SQL Server: Sum() Null -> 0 for calculation

[leinad31]

I had an issue awhile ago with SQL Server... I had wanted to do SUM() - SUM() but the second sum returned null (0 records). In access I could have done IIF(SUM() Is Null, 0, Sum()), how would I implement the same thing with SQL Server?

[Static]

CASE WHEN SUM(.....) IS NULL THEN 0 ELSE SUM(.....)



btw: in Access Nz(Sum(....),0)

Nz is like an IIF for nulls

[leinad31]

Is Nz available in all versions? I can't recall correctly if it was Nz exactly but I saw a similarly spelled function in the help but it couldnt be recorgnized in the access 9? query hence I used IIF().

[RobDog888]

In SQL you could also use the IsNull function.

IsNull(Sum(Field1),0)

[leinad31]

That would be cleaner. I'll try it out later. Tnx

[Static]

RobDog888 - Good to know!
didnt realize Isnull could funtion like that in SQL.

Thanks!

[RobDog888]

No prob. guys.

[DaveBo]

Isn't Coalesce the SQL equivalent of NZ?

[RobDog888]

From SQL BOL:

COALESCE
Returns the first nonnull expression among its arguments.

Syntax
COALESCE ( expression [ ,...n ] )

Arguments
expression

Is an expression of any type.

n

Is a placeholder indicating that multiple expressions can be
specified. All expressions must be of the same type or must be
implicitly convertible to the same type.

Return Types
Returns the same value as expression.

Remarks
If all arguments are NULL, COALESCE returns NULL.

COALESCE(expression1,...n) is equivalent to this CASE function:

CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL

Same or not same as nz? I'm not familiar with nz.

[Static]

Nz(Value, Val If Null, Vall If not Null)

Nz(x,0,x)


if x is null then 0 is returned, otherwize x

[RobDog888]

So then its similar, but not the same?

[szlamany]

IsNull(field,0) and Coalesce(field,0) will do the same thing - if field is null, the second item - 0 - is returned.

IsNull is frowned upon in purist SQL, since it's a MS function, not standard on other SQL's.

I use ISNULL anyway - I can't type Coalesce without thinking - everytime

[dRAMmer]

ISNULL and COALESCE are not always the same

SELECT ISNULL(SUM(Field1), 0) FROM Table1
SELECT COALESCE(SUM(Field1), 0) FROM Table1

are the same but not this:

SELECT ISNULL(Field1, 0) FROM Table1
SELECT COALESCE(Field1, 0) FROM Table1

the first select statement will return ZERO if table1 is empty (or if you have WHERE clause and it returns empty for that matter)
the second will give you NULL if table 1 is empty

[Bregalad]

Also COALESCE can be used for multiple values:

SELECT COALESCE(Field1,Field2,Field3,0) AS Result FROM Table1

This will return the first of the fields that isn't NULL,
or the number 0 if they all are NULL

[szlamany]

Originally posted by dRAMmer
ISNULL and COALESCE are not always the same

SELECT ISNULL(SUM(Field1), 0) FROM Table1
SELECT COALESCE(SUM(Field1), 0) FROM Table1

are the same but not this:

SELECT ISNULL(Field1, 0) FROM Table1
SELECT COALESCE(Field1, 0) FROM Table1

the first select statement will return ZERO if table1 is empty (or if you have WHERE clause and it returns empty for that matter)
the second will give you NULL if table 1 is empty

I tested what you said here - and it sure didn't behave the way you said it would!

Try this in QUERY ANALYZER:

GO
DROP TABLE NULLTEST_T
Go

CREATE TABLE NULLTEST_T
(RptSP		varchar(50)	NULL
,RptSeq		int		NULL)
GO
SELECT ISNULL(RPTSP,'IT IS NULL') FROM NULLTEST_T       -- These both return NO RECORDSET AT ALL
SELECT COALESCE(RPTSP,'IT IS NULL') FROM NULLTEST_T
GO
SELECT ISNULL(RPTSEQ,0) FROM NULLTEST_T                    -- These both return NO RECORDSET AT ALL
SELECT COALESCE(RPTSEQ,0) FROM NULLTEST_T
GO
INSERT INTO NULLTEST_T VALUES (NULL,NULL)
GO
SELECT ISNULL(RPTSP,'IT IS NULL') FROM NULLTEST_T       -- These both return "IT IS NULL"
SELECT COALESCE(RPTSP,'IT IS NULL') FROM NULLTEST_T
GO
SELECT ISNULL(RPTSEQ,0) FROM NULLTEST_T                    -- These both return "0"
SELECT COALESCE(RPTSEQ,0) FROM NULLTEST_T
GO

[dRAMmer]

I stand corrected, I recently had this code and I was doing a different thing...

again...

my apologies...