locked
Multiply Columns Multiple Tables RRS feed

  • Question

  • I am building a query to pull data from several table. One of the columns I want to show up in my query is not a column in any tables. It would be the product of 1 column from table 1 multiplied by a column from table 2. How would I go about this?

     

    SELECT transactions.Store, product.name, transactions.date, transactions.Qty
    FROM transactions, product
    WHERE transactions.product_code = product.product_code
    

     

    I want the new column (transactions.Qty * product.price) to show up after transcations.Qty

     

     


    • Edited by Waitzkin Monday, October 24, 2011 5:46 PM
    Monday, October 24, 2011 5:46 PM

Answers

  • Hello Waitzkin
    Just add your new column in the select
    SELECT transactions.Store, product.name, transactions.date, transactions.Qty
    , transactions.Qty * product.price FROM transactions, product
    WHERE transactions.product_code = product.product_code
    

    You may also want to name your columns. For each of them, use the AS keyword

    SELECT transactions.Store as STORE, .........

    • Proposed as answer by Naomi N Monday, October 24, 2011 5:50 PM
    • Edited by Sygrien Monday, October 24, 2011 5:50 PM
    • Marked as answer by Waitzkin Monday, October 24, 2011 5:55 PM
    Monday, October 24, 2011 5:49 PM

All replies

  • Hello Waitzkin
    Just add your new column in the select
    SELECT transactions.Store, product.name, transactions.date, transactions.Qty
    , transactions.Qty * product.price FROM transactions, product
    WHERE transactions.product_code = product.product_code
    

    You may also want to name your columns. For each of them, use the AS keyword

    SELECT transactions.Store as STORE, .........

    • Proposed as answer by Naomi N Monday, October 24, 2011 5:50 PM
    • Edited by Sygrien Monday, October 24, 2011 5:50 PM
    • Marked as answer by Waitzkin Monday, October 24, 2011 5:55 PM
    Monday, October 24, 2011 5:49 PM
  • See this
    SELECT TR.Store, P.name, TR.date, TR.Qty, TR.Qty * P.Price as [LineTotal]
    FROM dbo.transactions TR INNER JOIN dbo.product P
    ON TR.product_code = P.product_code
    
    I made a few more changes - say, introduced aliases to improve readability of the code and used INNER JOIN instead of old-style joins.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, October 24, 2011 5:53 PM
  • Excellent, Thank you.
    Monday, October 24, 2011 5:55 PM