SQL - Calculations on Alias Columns

[~J~]

Anyone have any ideas what I'm doing wrong here:

SELECT dbo.Comparison.seq,
dbo.Comparison.Material,
dbo.Comparison.MaterialType,
dbo.Comparison.MaterialDescription,
dbo.Comparison.MRPController,
dbo.Comparison.Rounding,
dbo.Comparison.Cost,
SUM(dbo.InventoryCheck.PlantStockAvailable) AS 'R3Stock',
0 as 'Physical',
R3Stock - Physical AS Diff

FROM dbo.Comparison
INNER JOIN dbo.InventoryCheck
ON dbo.Comparison.Material = dbo.InventoryCheck.MaterialCode
GROUP BY dbo.Comparison.seq,
dbo.Comparison.Material,
dbo.Comparison.MaterialType,
dbo.Comparison.MaterialDescription,
dbo.Comparison.MRPController,
dbo.Comparison.Rounding, dbo.Comparison.Cost
HAVING (dbo.Comparison.Material = N'EU-E35-11710A')

The error I get is that there is no column(s) called "R3Stock" and "Physical", which I know there isn't but I've created them as alias columns.

Any ideas how I can get the calculation of the two alias columns?

(btw: I know 'Physical' is zero at the moment)

 

[Haircut]

Just do SUM(dbo.InventoryCheck.PlantStockAvailable) - 0 AS Diff

Yes, you will have to change it if your R3Stock or Physical columns change their formulas but AFAIK that's the only way to do it.

 

[~J~]

Yeah you're right. Cheers for that. The only other option I thought of (seeing as there are going to be about another 10 columns based on calculations from the first 2) is to insert into a temporary table (whcih would then have column names and not aliases) and do the calculations there.

Cheers anyway.