CODEWITHSUNDEEP
sqlsql-server-2008
Dustin
Dustin

Reputation: 45

How to properly use Aliases

I am trying to use the aliases OriginalCost and QuantityDiscount to do some calculation. I understand that you can only use aliases in GROUP BY, HAVING, or ORDER BY, but I have not sure about how to do it the right way. The error I am getting with this code is

Line 9 Invalid column name 'QuantityDiscount'.

SELECT  Orders.Orderid, Inventory.partid, Description, Qty, (Inventory.price) AS UnitPrice, 
        (OrderItems.Qty * Inventory.price) AS OriginalCost, 
        CASE 
            WHEN OrderItems.Qty >= 5 THEN (OriginalCost) * .05)
            WHEN OrderItems.Qty >= 10 THEN (OriginalCost) * .10)
            ELSE 0
        END AS QuantityDiscount,
        SUM(OriginalCost  - QuantityDiscount) AS FinalCost
FROM Orders
JOIN OrderItems ON OrderItems.orderid = OrderItems.orderid
JOIN Inventory ON ORDERITEMS.partid = Inventory.partid
ORDER BY QTY DESC

Upvotes: 0

Views: 80

Answers (4)

ClearLogic
ClearLogic

Reputation: 3692

You cannot use alias defined in the same select statement. You will have to use sub-queries or table expression.

Something like this (code not tested but just to give you the idea)

SELECT * 
        ,SUM(t2.OriginalCost - t2.QuantityDiscount) AS FinalCost
FROM
  ( SELECT * 
           ,CASE WHEN t1.Qty >= 5 THEN (t1.OriginalCost * .05) WHEN t1.Qty >= 10 THEN (t1.OriginalCost * .10) ELSE 0 END AS QuantityDiscount
    FROM
          ( SELECT Orders.Orderid,
                   Inventory.partid,
                   Description,
                   Qty,
                   (Inventory.price) AS UnitPrice,
                   (OrderItems.Qty * Inventory.price) AS OriginalCost
           FROM Orders
           JOIN OrderItems ON OrderItems.orderid = OrderItems.orderid
           JOIN Inventory ON ORDERITEMS.partid = Inventory.partid 
           ) AS t1 

  ) AS t2
ORDER BY QTY DESC

Upvotes: 0

Ken White
Ken White

Reputation: 125748

You didn't provide any sample data, so this is untested:

SELECT 
  t.OrderID,
  t.PartID,
  t.Description,
  t.Qty,
  t.UnitPrice,
  t.OriginalCost,
  t.QuantityDiscount,
  SUM(t.OriginalCost - t.QuantityDiscount) AS FinalCost
FROM (
    SELECT  
      Orders.Orderid, 
      Inventory.partid, 
      Description, 
      Qty, 
      (Inventory.price) AS UnitPrice, 
      (OrderItems.Qty * Inventory.price) AS OriginalCost, 
      CASE 
        WHEN OrderItems.Qty >= 5 THEN (OrderItems.Qty * Inventory.Price) * .05)
        WHEN OrderItems.Qty >= 10 THEN (OrderItems.Qty * Inventory.Price) * .10)
        ELSE 0
      END AS QuantityDiscount
    FROM 
      Orders
    JOIN 
      OrderItems ON OrderItems.orderid = OrderItems.orderid
    JOIN 
      Inventory ON ORDERITEMS.partid = Inventory.partid
) AS t
ORDER BY QTY DESC

Upvotes: 1

Pieter Geerkens
Pieter Geerkens

Reputation: 11893

Try this instead:

SELECT
  Orderid,
  partid,
  Description,
  Qty,
  UnitPrice,
  OriginalCost,
  QuantityDiscount,
  OriginalCost  - QuantityDiscount AS FinalCost
FROM (
  SELECT  
    Orders.Orderid, Inventory.partid, Description, Qty, 
    Inventory.price AS UnitPrice, 
    (OrderItems.Qty * Inventory.price) AS OriginalCost, 
    CASE 
       WHEN OrderItems.Qty >= 5 THEN (OriginalCost) * .05)
       WHEN OrderItems.Qty >= 10 THEN (OriginalCost) * .10)
       ELSE 0
    END AS QuantityDiscount
  FROM Orders
) T
JOIN OrderItems ON OrderItems.orderid = OrderItems.orderid
JOIN Inventory ON ORDERITEMS.partid = Inventory.partid
ORDER BY QTY DESC

If you still need amy aggregations at this point, wrap another query around this one as a sub-query.

Upvotes: 0

Pirion
Pirion

Reputation: 519

So you can define aliases the following ways:

SELECT Alias = TableAlias.Field         --prefixed to a field
       ,TableAlias.Field as OtherAlias  --at the end of a field
FROM Table TableAlias                   --on a table
--then use by anything that is caculated after that part in the order of operations.
ORDER BY Alias;

Then you can use them after the area they are defined in.

SQL Order of Operations:

  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause

Upvotes: 1

Related Questions