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') 6 28301
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
> 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
--
>> 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
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)
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
--
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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.
|
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.
|
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
|
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...
|
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)
....
| |
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?
|
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...
|
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'
|
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)
{
|
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...
|
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,...
| |
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...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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...
| |