locked
Use value of a calculated column in a another column RRS feed

  • Question

  • User-172769993 posted

    for example from below query how can i get Total_Amount in this format

    @Amount + p.Shipping_Cost + p.Customs_Cost AS [Total_Amount], 

    instead of below format

    SUM(pd.UnitPrice * pd.Quantity) + p.Shipping_Cost + p.Customs_Cost AS [Total_Amount]

    Query

    SELECT
      p.Purchase_ID,
      s.Supplier_Name,
      p.InvoiceNo,
      SUM(pd.UnitPrice * pd.Quantity) + p.Shipping_Cost + p.Customs_Cost AS [Total_Amount],
      SUM(pd.UnitPrice * pd.Quantity) AS Amount,
      p.Shipping_Cost,
      p.Customs_Cost,
      p.Purchase_Date,
      p.Expected_Delivery_Date,
      p.Actual_Delivery_Date,
      p.Delivery_Location,
      p.Delivery_Status,
      p.PaidBy,
      p.AmountPaid
    FROM dbo.Stock_Purchase p
    INNER JOIN dbo.Stock_Purchase_Details pd
      ON p.Purchase_ID = pd.Purchase_ID
    INNER JOIN dbo.Supplier s
      ON p.Supplier_ID = s.Supplier_ID
    GROUP BY p.Purchase_ID,
             s.Supplier_Name,
             p.InvoiceNo,
             p.Shipping_Cost,
             p.Purchase_Date,
             p.Expected_Delivery_Date,
             p.Actual_Delivery_Date,
             p.Delivery_Location,
             p.Delivery_Status,
             p.Shipping_Cost,
             p.Customs_Cost,
             p.PaidBy,
             p.AmountPaid

     



    Saturday, April 30, 2016 1:04 PM

Answers

  • User77042963 posted
    Select 
      Purchase_ID,
      supplier_Name,
      InvoiceNo,
      Amount + Shipping_Cost + Customs_Cost AS [Total_Amount],
       Amount,
      Shipping_Cost,
      Customs_Cost,
      Purchase_Date,
      Expected_Delivery_Date,
      Actual_Delivery_Date,
      Delivery_Location,
      Delivery_Status,
      PaidBy,
      AmountPaid
    
    from (
    SELECT
      p.Purchase_ID,
      s.Supplier_Name,
      p.InvoiceNo,
     
      SUM(pd.UnitPrice * pd.Quantity) AS Amount,
      p.Shipping_Cost,
      p.Customs_Cost,
      p.Purchase_Date,
      p.Expected_Delivery_Date,
      p.Actual_Delivery_Date,
      p.Delivery_Location,
      p.Delivery_Status,
      p.PaidBy,
      p.AmountPaid
    FROM dbo.Stock_Purchase p
    INNER JOIN dbo.Stock_Purchase_Details pd
      ON p.Purchase_ID = pd.Purchase_ID
    INNER JOIN dbo.Supplier s
      ON p.Supplier_ID = s.Supplier_ID
    GROUP BY p.Purchase_ID,
             s.Supplier_Name,
             p.InvoiceNo,
             p.Shipping_Cost,
             p.Purchase_Date,
             p.Expected_Delivery_Date,
             p.Actual_Delivery_Date,
             p.Delivery_Location,
             p.Delivery_Status,
             p.Shipping_Cost,
             p.Customs_Cost,
             p.PaidBy,
             p.AmountPaid ) t
    	    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, May 1, 2016 6:38 PM
  • User753101303 posted

    Hi,

    You could likely also use:

    - a CTE: https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx (which is basically the same allowing to name a sub query and reuse it in the rest of the query, could be compared so to speak to a kind of "temporary view")
    - a computed column : https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx in which case you could have a read only Amount column.

    The basic idea is that the Amount pseudo-column can't be defined and used in the same "context" (ie SELECT). It needs to be defined first before being consumed.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 2, 2016 1:10 PM

All replies

  • User77042963 posted
    Select 
      Purchase_ID,
      supplier_Name,
      InvoiceNo,
      Amount + Shipping_Cost + Customs_Cost AS [Total_Amount],
       Amount,
      Shipping_Cost,
      Customs_Cost,
      Purchase_Date,
      Expected_Delivery_Date,
      Actual_Delivery_Date,
      Delivery_Location,
      Delivery_Status,
      PaidBy,
      AmountPaid
    
    from (
    SELECT
      p.Purchase_ID,
      s.Supplier_Name,
      p.InvoiceNo,
     
      SUM(pd.UnitPrice * pd.Quantity) AS Amount,
      p.Shipping_Cost,
      p.Customs_Cost,
      p.Purchase_Date,
      p.Expected_Delivery_Date,
      p.Actual_Delivery_Date,
      p.Delivery_Location,
      p.Delivery_Status,
      p.PaidBy,
      p.AmountPaid
    FROM dbo.Stock_Purchase p
    INNER JOIN dbo.Stock_Purchase_Details pd
      ON p.Purchase_ID = pd.Purchase_ID
    INNER JOIN dbo.Supplier s
      ON p.Supplier_ID = s.Supplier_ID
    GROUP BY p.Purchase_ID,
             s.Supplier_Name,
             p.InvoiceNo,
             p.Shipping_Cost,
             p.Purchase_Date,
             p.Expected_Delivery_Date,
             p.Actual_Delivery_Date,
             p.Delivery_Location,
             p.Delivery_Status,
             p.Shipping_Cost,
             p.Customs_Cost,
             p.PaidBy,
             p.AmountPaid ) t
    	    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, May 1, 2016 6:38 PM
  • User753101303 posted

    Hi,

    You could likely also use:

    - a CTE: https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx (which is basically the same allowing to name a sub query and reuse it in the rest of the query, could be compared so to speak to a kind of "temporary view")
    - a computed column : https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx in which case you could have a read only Amount column.

    The basic idea is that the Amount pseudo-column can't be defined and used in the same "context" (ie SELECT). It needs to be defined first before being consumed.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 2, 2016 1:10 PM