SUM evaluating to NULL 2

[Rhys666]

OK, How can I make a SUM statement evaluate to NULL if one of the SUM'd records is NULL? Or an equivalent of course...

I have a table with four fields, we'll call them id, date, time, readvalue. Id is a foreign key to a table holding meter information to which the reading pertains. Each meter belongs to a site, and a site can have between 1 and 10 meters. Using SUM and GROUP BY I can create aggregate SUM information. The problem is that a missing read value is represented by a NULL, (as 0 could be a valid read value, as can both negative and positive values), and for each date and time period if one meter belonging to a site has a NULL read value, the site level date/period read value should be derived as NULL. In case you want to know, there are 96 read periods in a day, (1/15 minutes)

Table Structure
MeterId ReadDate TimeIndex ReadValue
int datetime int decimal(19,10)

Aggregating Query

select
	m.SiteLevelId,
	mr.ReadDate,
	mr.TimeIndex,
	SUM(mr.ReadValue) as 'SiteReadValue'
from
	MeterReadingTable mr INNER JOIN MeterTable m ON mr.Id = m.Id
group by
	m.SiteLevelId,
	mr.ReadDate,
	mr.TimeIndex
GO

SiteLevelId is a foreign key on the meter table, referencing the site to which it belongs, (one to many as a site can have many meters)

Any idea's?

I'd like to avoid using a cursor, which I'm sure I could to get around this, as it's going to be pretty convoluted and probably very innefficient if I go that route.

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]

this one's easy

as you know, aggregate functions ignore nulls

so if you SUM() a column of values, let's say there's 10 rows, and one of them is null, then the SUM() is the total of the 9 non-null values, conveniently ignoring the null

and COUNT(colname) works the same, it will return 9

but COUNT(*) counts rows, and it returns 10!

so the trick is,

select case when count(mr.ReadValue)
= count(*)
then sum(mr.ReadValue)
else null end as SiteReadValue



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

 

[Rhys666]

It's been a long day and week, (one particularly bad wednesday stretching it out horribly and I'm moving house tomorrow), so that answer is SO much appreciated - suddenly I'm all relaxed!

Sometimes you can't see the wood for the tree's, thank you

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

 

[donutman]

I haven't seen that one rudy, nice.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]