SUM evaluating to NULL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SUM evaluating to NULL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-28 : 07:45:57

Rhys writes "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."

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2005-02-28 : 09:14:25

[code]
case when count(*) = count(mr.ReadValue) then SUM(mr.ReadValue) end as SiteReadValue
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources