Sum & NULL

[Rhys666]

NULL values are ignored when using SUM in Sql Server.

As my brain is failing to register what I need at the moment, what have people found as the best way to SUM a field and return a NULL result when there is a NULL value in those being SUM'd?

Cheers!

Rhys

""Vampireware /n/, a project, capable of sucking the lifeblood out of anyone unfortunate enough to be assigned to it, which never actually sees the light of day, but nonetheless refuses to die."

My Home

 

[r937]

[tt]select sum(foo)/count(foo)[/tt] should do it



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[r937]

oh, wait a sec, that was wrong

how about this

[tt]select sum(foo)
* case when count(foo)=count(*)
then 1 else null end[/tt]

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[JamesLean]

I started this thinking it was simple and then realised it was actually harder than I thought! But I came up with this, interested to see if anyone else has another solution:

DECLARE @t table (id int, val int)

INSERT @t VALUES (1, 4)
INSERT @t VALUES (1, 3)
INSERT @t VALUES (2, 10)
INSERT @t VALUES (2, 1)
INSERT @t VALUES (2, NULL)

--normal (ignores NULLs)
SELECT id, SUM(val)
FROM @t
GROUP BY id

--solution (returns NULLs)
SELECT id, SUM(val)
FROM (
		SELECT id, CASE WHEN EXISTS (SELECT * FROM @t WHERE id = t1.id AND val IS NULL) THEN NULL ELSE val END AS val
		FROM @t t1
	) t2
GROUP BY id

--James

 

[JamesLean]

Nice Rudy, I like that. ;-)

--James

 

[Rhys666]

Almost does what I want, so I'm playing - cheers!

My structure is such that I have Site records, each of which has a number of child Measuring Device records. Each measuring device receives 96 readings, (numeric scale 8 precision 5), per day taken at 15 minute intervals.

I need to sum each of the measuring devices individual daily period readings to garner a site level daily period reading, excepting that where a measuring device reading is null, the site level aggregate should also be null.

The Site table is the top level, the measuring device table has a foreign key set to the id field of site table, (one to many relationship, site to device), and for each measuring device and day a set of reading records is stored in a reading table with a foreign key to the measuring device table and unique index on measuring device id and date. other than that the reads table has an horrendous 96 read period fields, i.e.,
Site: [SiteId] [Some Other Detail]
Device: [DeviceId] [Some Other Details] [SiteId]
Device Reads: [DeviceId] [Date] [1] [2] .... [96]

so, for all devices belonging to a site, I need to sum the values for each period field, i.e., Sum([1]), Sum[2], etc but again aggregating to NULL where the value for a single device, day and period belonging to a site is NULL.

I'm thinking of tweaking what you've done and simply summing (based on your check for null values) and storing each site, date, period read value in a variable before a simple inserting into my site level reads table which is of the same structure to the device reads table excepting the foreign key is the Site id, (again index on id and date).

Cheers again for pointing my over-tired brain in what seems a logical direction, but hey at least it's friday and i've got the weekend off!

Rhys

""Vampireware /n/, a project, capable of sucking the lifeblood out of anyone unfortunate enough to be assigned to it, which never actually sees the light of day, but nonetheless refuses to die."

My Home

 

[ProdAir]

How about:

SELECT CASE WHEN (SUM(Col1))/COUNT(*) - AVG(CAST(Col1))) < 0 THEN NULL
ELSE SUM(Col1)
END

COUNT doesn't ignore NULLs so the average you get by dividing your sum by it should be less if it has a NULL. Hope this helps.