June 5, 2003 at 12:25 pm
why does this return ERROR "invalid column name 'profit' "
code:
Sum([intquantity]*[curcost]) AS Cost, Sum([intquantity]*[curprice]) AS Price, sum(([intquantity]*[curPrice])-([intquantity]*[curcost]))AS Profit, sum((([Profit]/[Price])*10000)/100) AS [ProfitPercent]
"The grass is always greener over the septic tank." ~Leaf
June 5, 2003 at 12:31 pm
You don't want the SUM in the percent part, plus you can't use aliases in the SUM expression...must use the full expression. If you don't want to do this repeatedly, check out computed columns in BOL:
Sum([intquantity]*[curcost]) AS Cost, Sum([intquantity]*[curprice]) AS Price, sum(([intquantity]*[curPrice])-([intquantity]*[curcost]))AS Profit, ((sum(([intquantity]*[curPrice])-([intquantity]*[curcost]))/Sum([intquantity]*[curprice]))*10000)/100) AS [ProfitPercent]
[/code]
June 5, 2003 at 1:54 pm
ok, how do i avoid the ERROR:
"divide by zero error encountered"
"The grass is always greener over the septic tank." ~Leaf
June 5, 2003 at 1:56 pm
quote:
ok, how do i avoid the ERROR:"divide by zero error encountered"
Use a CASE expression in your SELECT to test whether the denominator is equal to zero before you perform the operation:
SELECT
CASE WHEN Denominator = 0 THEN 1
ELSE Numerator/Denominator END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply