Answered by:
Not sure why this doesn't work....
Question

I'm attempting to compute a Net profit within my sql by calculating first the Expense, then the Revenue, and then subtracting the two columns.
The following is the code that does NOT work:
Select Left(Task, 5) as Project, Sum(Amount*0.29) as Expense, Sum(Amount*1.3) as ContRevenue,(ContRevenue  Expense) as NetProfitfrom GLdetail
group by Left(Task, 5)
The error for both 'ContRevenue' and 'Expense' is 'invalid column name'. This error occurs in the parenthesis where I attempt to subtract expense from ContRevenue. If I take that portion out, it works fine. If I replace those column names with the actual calculation, i.e. sum(x)  sum(x), it also works fine.
I'm curious as to why this doesn't work?
TIA
Monday, March 26, 2007 3:23 PM
Answers

You are assigning an ALIAS to the product of [ sum(Amount*1.3) ] and the product of [ sum(Amount*.029) ], and then attempting to use those products in the NetProfit calculation.
Those calculations are not complete and available at that moment.
I suggest that you might first calculate the products that are assigned to ALIASes, and then calculate the NetProfit. Perhaps something like (untested):
SELECT
dt.Project,
dt.Expense,
dt.ContRevenue,
NetProfit = ( dt.ContRevenue  dt.Expense)
FROM (SELECT
Project = left(Task, 5),
Expense = sum(Amount*0.29),
ContRevenue = sum(Amount*1.3)
FROM GLdetail
GROUP BY left(Task, 5)
) dt
ORDER BY ProjectMonday, March 26, 2007 3:53 PM 
If you replace the Aliases with the actual calculations, it should work fine:
Select Left(Task, 5) as Project, Sum(Amount*0.29) as Expense, Sum(Amount*1.3) as ContRevenue,((Sum(Amount*1.3) )  (Sum(Amount*1.3))) as NetProfit
from GLdetail
group by Left(Task, 5)
As Arnie has said, the Aliases are not assigned a value until after the query runs, so the calculations are null. If you assigned the calculations to an alias in a subquery, the subquery would run first, and at that point you could reference just the Alias.
However, in the example you've provided, and for all intents and purposes, you could simply use the above query.
Adamus
Monday, March 26, 2007 4:13 PM
All replies

You are assigning an ALIAS to the product of [ sum(Amount*1.3) ] and the product of [ sum(Amount*.029) ], and then attempting to use those products in the NetProfit calculation.
Those calculations are not complete and available at that moment.
I suggest that you might first calculate the products that are assigned to ALIASes, and then calculate the NetProfit. Perhaps something like (untested):
SELECT
dt.Project,
dt.Expense,
dt.ContRevenue,
NetProfit = ( dt.ContRevenue  dt.Expense)
FROM (SELECT
Project = left(Task, 5),
Expense = sum(Amount*0.29),
ContRevenue = sum(Amount*1.3)
FROM GLdetail
GROUP BY left(Task, 5)
) dt
ORDER BY ProjectMonday, March 26, 2007 3:53 PM 
If you replace the Aliases with the actual calculations, it should work fine:
Select Left(Task, 5) as Project, Sum(Amount*0.29) as Expense, Sum(Amount*1.3) as ContRevenue,((Sum(Amount*1.3) )  (Sum(Amount*1.3))) as NetProfit
from GLdetail
group by Left(Task, 5)
As Arnie has said, the Aliases are not assigned a value until after the query runs, so the calculations are null. If you assigned the calculations to an alias in a subquery, the subquery would run first, and at that point you could reference just the Alias.
However, in the example you've provided, and for all intents and purposes, you could simply use the above query.
Adamus
Monday, March 26, 2007 4:13 PM 
The following query work for you..
Select
Left(Task, 5) as Project,
Sum(Amount*0.29) as Expense,
Sum(Amount*1.3) as ContRevenue,
(Sum(Amount*1.3)  Sum(Amount*0.29)) as NetProfit
from
GLdetail
group by
Left(Task, 5)Monday, March 26, 2007 5:43 PM