Question about SUM and Nulls

[mike]

I'm doing what I thought was a simple GROUP BY summary of fairly simple
data and the my numbers aren't working out

Some results are showing up <NULL> when I know the data is in the
database

I'm no SQL expert, but if I'm summing (SUM) multiple fields and adding
them together in my SELECT how does SUM handle Null? In some situations a
single column in a single row is Null but and it's part of a larger GROUP BY
and SUM and from looking it over I have a guess it's a problem with SUM
handling Null

I'm not sure the SQL will help you without the schema but here it is
anyways

thanks

mike

PS The schema can be found here...but I don't think you'll need it. So what
if in one row that is being grouped Batting.IBB is null?

SELECT Master.playerID , Master.nameFirs t, Master.nameLast ,
Batting.teamID, SUM(Batting.AB) + SUM(Batting.BB) + SUM(Batting.IBB ) +
SUM(Batting.HBP )
+ SUM(Batting.SH) + SUM(Batting.SF) AS PA
FROM Master INNER JOIN
Batting ON Master.playerID = Batting.playerI D
GROUP BY Master.playerID , Master.nameFirs t, Master.nameLast , Batting.teamID
HAVING (Batting.teamID = N'CIN' OR
Batting.teamID = N'CN2')

[Ryan]

Re: Question about SUM and Nulls

A NULL is not the same as any other NULL
A NULL is not = ''
A NULL is not = 0
If you SUM a NULL then it won't know if this is a zero, or any other
number.

So, try SUM(ISNULL(Your Column, 0)) which will replace any null values
with a zero and include these zero's in the SUM.

Ryan

[David Portas]

Re: Question about SUM and Nulls

> So, try SUM(ISNULL(Your Column, 0)) which will replace any null values
[color=blue]
> with a zero and include these zero's in the SUM.[/color]

Better use ISNULL(SUM(Your Column),0). SUM ignores NULLs anyway so there
is no need to force ISNULL to be performed for each row.

I usually prefer COALESCE over ISNULL because it's Standard SQL (ISNULL
isn't) and more powerful than its near-equivalent.

--
David Portas
SQL Server MVP
--

[mike]

Re: Question about SUM and Nulls

>> So, try SUM(ISNULL(Your Column, 0)) which will replace any null values[color=blue]
>[color=green]
>> with a zero and include these zero's in the SUM.[/color]
>
> Better use ISNULL(SUM(Your Column),0). SUM ignores NULLs anyway so there
> is no need to force ISNULL to be performed for each row.[/color]

if SUM ignores NULLs then that must not be the problem with my query??

mike

[Hugo Kornelis]

Re: Question about SUM and Nulls

On Wed, 23 Feb 2005 11:49:54 -0800, mike wrote:
[color=blue]
> if SUM ignores NULLs then that must not be the problem with my query??[/color]

Hi Mike,

If the data in a group (as formed by GROUP BY) has some NULLs and some
non-NULL data, the NULLs are ignored and the result is the sum of the
remaining numbers: SUM {1, 3, NULL, 5} = SUM {1, 3, 5} = 9
If all data in the group is NULL, the NULLs are ignored as well, leaving
no rows to be summed at all: the result is the sum of the empty set; by
definition this is NULL. SUM {NULL, NULL} = SUM {} = NULL.

In your case, you use SUM(Batting.AB) + SUM(Batting.BB) + ... If you
have a group of three rows, and AB is NULL for one of these rows but BB
is NULL for all rows, then the result will be SUM {1, 3, NULL} + SUM
{NULL, NULL, NULL} + ... = SUM {1, 3} + SUM {} + ... = 4 + NULL + ... =
NULL (since the result of any exppression involving NULL is NULL by
definition).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[David Portas]

Re: Question about SUM and Nulls

SUM does ignore NULLs but if EVERY row is NULL then the result will
also be NULL. Did you try:

COALESCE(SUM(Ba tting.AB),0) + COALESCE(SUM(Ba tting.BB),0) + ... etc

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Re: Question about SUM and Nulls

David Portas (REMOVE_BEFORE_ REPLYING_dporta s@acm.org) writes:[color=blue][color=green]
>> So, try SUM(ISNULL(Your Column, 0)) which will replace any null values[/color]
>[color=green]
>> with a zero and include these zero's in the SUM.[/color]
>
> Better use ISNULL(SUM(Your Column),0). SUM ignores NULLs anyway so there
> is no need to force ISNULL to be performed for each row.[/color]

Unless you are bothered by the message "Warning: Null value is eliminated by
an aggregate or other SET operation.", that is.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp