Invalid column name 'FinalTotal'

Question

SELECT 
    o.OrderID, ProductName, OrderDate, ItemPrice, TaxAmount, 
    DiscountAmount, DiscountAmount - ItemPrice AS FinalTotal, 
    Quantity, ShipDate,FinalTotal * Quantity AS ItemTotal
FROM
    Orders AS o 
JOIN 
    OrderItems AS oi ON o.OrderID = oi.OrderID
JOIN 
    Products AS p ON oi.ProductID = p.ProductID;

Here is my code, it keeps saying invalid column name 'FinalTotal'. What am I doing wrong here?

Answer 1

you can't use a column alias on the same query level like that.

use the full expression of FinalToal in the calculation of ItemTotal

SELECT o.OrderID, ProductName, OrderDate, ItemPrice, TaxAmount, DiscountAmount,
       DiscountAmount - ItemPrice AS FinalTotal, 
       Quantity, ShipDate,
       (DiscountAmount - ItemPrice) * Quantity AS ItemTotal
FROM Orders AS o JOIN OrderItems AS oi
    ON o.OrderID = oi.OrderID
 JOIN Products AS p
    ON oi.ProductID = p.ProductID;

alternatively is to use derived query or CTE

Answer 2

One trick for doing this in SQL Server is to use apply:

SELECT o.OrderID, ProductName, OrderDate, ItemPrice, TaxAmount, 
       DiscountAmount, x.FinalTotal, Quantity, ShipDate,
       x.FinalTotal * Quantity AS ItemTotal
FROM Orders o JOIN
     OrderItems AS oi
     ON o.OrderID = oi.OrderID JOIN
     Products AS p
     ON oi.ProductID = p.ProductID CROSS APPLY
     (SELECT (DiscountAmount - ItemPrice) as FinalTotal) x