July 23, 2008 at 10:43 am
And I would STRONGLY urge you to use a CASE statement to verify your QuantityShipped is not NULL or Zero or you will, at some point in the future, get a NULL result or Divide By Zero Error.
Best practice is to code for the assumption that one or both will slip through even the most rigorous of data validation checks. So using the ISNULL() function inside your SUM() functions is also a good idea.
July 23, 2008 at 10:57 am
Is this okay?
CASE WHEN ISNULL(SUM(QuantityShipped), 0) = 0 THEN 0
ELSE SUM(T_OrderLine.[Unit Price] * T_OrderLine.QuantityShipped) / SUM(T_OrderLine.QuantityShipped) END AS [A.S.P]
July 23, 2008 at 11:23 am
B_Boy,
Syntax-wise, it is acceptible to put the ISNULL outside of the SUM, but practically speaking, it's a bad idea.
If there is one single NULL value in the column you are summing, then it could NULL the whole result set and then applies the ISNULL function which results in what ever value you're applying ISNULL() to.
I wrote up an example, but for some reason can't get it to work today. Which is weird, because every time I do it for real, the reversal does kick me in the butt...
Here's the example anyway, even if both results are coming out with values (darnit...)
Create table #MyTemp(MyID int identity(1,1) NOT NULL, UnitPrice money NULL)
GO
Insert into #MyTemp(UnitPrice)
(Select 1 UNION Select 15 UNION Select 38 UNION Select NULL)
Select * from #MyTemp
Select ISNULL(SUM(UnitPrice),0) as MyUnitPrice
from #MyTemp
Select SUM(ISNULL(UnitPrice,0)) as MyUnitPrice2
from #MyTemp
GO
Drop Table #MyTemp
July 23, 2008 at 11:32 am
Wow, I did not read your response carefully enough...
Is this okay
CASE WHEN ISNULL(SUM(QuantityShipped), 0) = 0 THEN 0
ELSE SUM(T_OrderLine.[Unit Price] * T_OrderLine.QuantityShipped) / SUM(T_OrderLine.QuantityShipped) END AS [A.S.P]
When I say use the ISNULL() function, I mean use it in the math as well, not just the determination of the CASE statement. So you want to use ISNULL() around every single column in that list. Look at my very first post in this thread and you'll see what I did. Even though I didn't SUM() the divisior, you can still mimic that code (mostly) for a proper way of accounting for possible NULLS.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply