473,714 Members | 3,077 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Question about SUM and Nulls

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')
Jul 23 '05 #1
6 28299
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

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


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
--

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


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.


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

mike
Jul 23 '05 #4
On Wed, 23 Feb 2005 11:49:54 -0800, mike wrote:
if SUM ignores NULLs then that must not be the problem with my query??


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)
Jul 23 '05 #5
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
--

Jul 23 '05 #6
David Portas (RE************ *************** *@acm.org) writes:
So, try SUM(ISNULL(Your Column, 0)) which will replace any null values

with a zero and include these zero's in the SUM.


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.


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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
6168
by: A.M. de Jong | last post by:
Reading a lot about Nulls right now I still can't find a Technical reason to use it or not. For what I've understand is this: In an Ingres database a Null column has a standard extra storage of 2 Bits. In a SQL Server database every column has a NULL-bit telling about this column it is NULL or NOT. That means that a varchar-empty NULLABLE column takes no space at all since the Nullable column defines it as NULL.
1
347
by: Droope | last post by:
I have a date field in a SQL table that allows null. If certain conditions are met in the user interface I need to set the field to nulls. using a typed dataset I do the following Dim rOrgPolicyRow As dsTyped.orgpolicyRow = tOrgPolicy.NeworgpolicyRow I am attempting to set the field = to null using the following code.
2
4355
by: mike.usenet | last post by:
Hi, I'm new to DB2, but am relatively familiar with databases, and I've run into something I don't fully understand. I created a table tab4, then, after the fact, altered the table so that one of the columns must not contain null values. I used the command: db2inst1@reihe51:~> db2 "alter table tab4 ADD CHECK (sp1 is NOT NULL)" // using "alter table add constraint" didn't work
12
2978
by: Brian Henry | last post by:
first question... I have a flat file which unfortinuatly has columns seperated by nulls instead of spaces (a higher up company created it this way for us) is there anyway to do a readline with this and not have it affected by the null? because it is right now causes truncated data at wierd places... but as soon as i manually with a hex editor change char(00) to char(20) in the files it reads prerfectly... which leads me to my 2nd...
6
1417
by: mrmagoo | last post by:
I have a class that accepts String and Long datatypes. Class Menu Private FirstTier as String Private SecondTier as Long Sub New(ByVal FirstTier As String, _ ByVal SecondTier As Long) ....
3
2638
by: Bob Stearns | last post by:
I am creating an index on a column which is 40% NULLS. The process seems to run forever, though a count of the number of values runs in milliseconds. This leads to the subject question: is there a way to ignore those rows with nulls in index creation?
3
1927
by: Zach | last post by:
Hello, This might be a rather basic question, but I've tried a few things and I can't really find a solution as elegant as what I'd like for this problem. The situation is this - I have a file that's written to disk in a binary format. Basically it's a bunch of records, one after the other, where each record has the following format : 18 bytes, 6 bytes which are all zero, 4 bytes which represent a UInt32. The first 18 bytes of each...
2
1268
by: bobh | last post by:
Hi All, In AccessXP I have a query and on the query grid in the Outcome(text field) field I put the criteria of <>"ToArb" when a record is open this field is null and when the record is closed the users has a combo box of values to select, one being 'ToArb'
0
1178
by: jehugaleahsa | last post by:
Try this instead: // T - the type you are attempting to retrieve. // o - the data that you want to turn into a T T get<T>(object o) { // handle nulls - turns Nullables into null, reference types to null and value types to 0. if (o == null || o == DBNull.Value) {
0
8808
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8712
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9316
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9077
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
5959
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4466
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4729
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2525
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2113
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.