SUM and ISNULL

[dragonwell]

Can someone explain the difference (if there is one) between the following two queries:

SELECT c.CusID,
SUM(ISNULL(p.Amount, 0)) AS Payments
FROM Customer c
LEFT OUTER JOIN Payment p ON p.CusID = c.CusID
GROUP BY c.CusID


SELECT c.CusID,
ISNULL(SUM(p.Amount), 0) AS Payments
FROM Customer c
LEFT OUTER JOIN Payment p ON p.CusID = c.CusID
GROUP BY c.CusID

Thanks...

 

[DotNetGnat]

if p.Amount is null it is set to 0 in the below case
ISNULL(p.Amount, 0))

so a zero value is added when SUM is done...which should be fine...

-DNG

 

[SQLBill]

In the first case, any NULL values in the column p.Amount are 'set' to 0, then they are SUMmed.

In the second case, all the values of p.Amount are SUMmed and if the SUM is NULL then it's changed to 0.

-SQLBill

Posting advice: FAQ481-4875

 

[vongrunt]

1st query is slightly slower than 2nd one, but safe against ANSI warnings - because SUM() never encounters NULL value.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

And if you don't want to see those warning use
SET ANSI_WARNINGS OFF

SET ANSI_WARNINGS affects these conditions:

When ON, if null values appear in aggregate functions (such as SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT) a warning message is generated. When OFF, no warning is issued.

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[dragonwell]

Wow - thanks for all the quick replies. That all goes along with what I was thinking. The confusion came up because I ran into the second form in another query:

SELECT ISNULL(SUM(p.Amount), 0) AS TotalPayments
FROM Payment p WHERE p.CusID = @CusID

which returns 0 if no records exist. But this is not:

SELECT SUM(ISNULL(p.Amount, 0)) AS TotalPayments
FROM Payment p WHERE p.CusID = @CusID

which returns null if no records exist (and is a senseless query).
It all makes sense now