January 19, 2006 at 5:59 pm
I have the following two lines in a select statement
sum((CASE when dbo.vw_SumContentPack.[Date] is null then 0 else dbo.vw_SumContentPack.Sales end)) AS Sales,
sum(isnull(dbo.vw_SumContentPack.Sales,0)) AS Sales
On first glance you would say they return the same value but that is not true sometimes the second line returns a value 1 even if there is no record.
My question is this why don't they return the same result?
Thanks in Advance,
Martijn Schiferli
January 19, 2006 at 7:41 pm
The second sum doesn't know when dbo.vw_SumContentPack.[Date] is null.
Try adding to the where clause
WHERE NOT(dbo.vw_SumContentPack.[Date] IS NULL)
Everett Wilson
ewilson10@yahoo.com
January 19, 2006 at 7:46 pm
Alternatively, the first sum should be dbo.vw_SumContentPack.[Sales], instead of dbo.vw_SumContentPack.[Date]?
January 19, 2006 at 8:02 pm
I have tried that but for some reason if I use the [Sales] field I also get a return value of 1. [Sales] is of declared as int and [Date] as datetime.
January 19, 2006 at 9:48 pm
By rewriting your statements, I get the following:
select sum(Sales)
from dbo.vw_SumContentPack
where [Date] IS NOT NULL
select sum(isnull(Sales,0))
from dbo.vw_SumContentPack
Is it? If so, then the differences are quite plain!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply