August 24, 2005 at 10:39 am
When i do a group by statement like
select client,productcode,modifiedlot,location,sku,count(*) ct,cast(sum(grosswgt) as decimal(18,4)) grosswgt,
cast(sum(netwgt) as decimal(18,4)) netwgt, cast(sum(avail) as decimal(18,4)) avail,cast(sum(onhand) as decimal(18,4)) onhand
from or51import group by client,productcode,modifiedlot,location,sku
I got the message:
Warning: Null value is eliminated by an aggregate or other SET operation.
Add up all values in count(*), the sum is equal to total row in the table, thus I have not missed a row. Is this a warning I need to worry about, or my aggregation result is good and nothing really to worry.
August 24, 2005 at 10:54 am
Are there any nulls hiding in the columns you are adding up?
e.g. select * from or51import where grosswgt is null?
August 24, 2005 at 11:27 am
Assuming that you want any NULLs to be summed as zeroes, use the isnull() function to get rid of the worrying error:
select client,productcode,modifiedlot,location,sku,count(*) ct,cast(sum(isnull(grosswgt,0)) as decimal(18,4)) grosswgt,
cast(sum(isnull(netwgt,0)) as decimal(18,4)) netwgt, cast(sum(isnull(avail,0)) as decimal(18,4)) avail,cast(sum(isnull((onhand,0)) as decimal(18,4)) onhand
from or51import group by client,productcode,modifiedlot,location,sku
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply