Calculate value from two field in third field

Question

I'm trying to create a query, that will calculate sum of products on invoice. I have 3 tables :

Product (with product's price)
Invoice (with invoice id)
Products on invoice (with invoice id, product id and number of particular products)

So in my query I take invoice_id (from invoice), price (from product),number of products sold and invoice_id (from products on invoice) and calculate their product in fourth column. I know I should use 'Totals' but how to achieve that ?

Is amount the total dollar amount that the product cost, or is amount the price? You should really be storing the price and amount in the products_on_invoice table in addition to the product table so that you can change the product's price without affecting past products_on_invoice entries. — Nitrodist
amount is the number of products with particular id on invoice. So each line looks like this : invoice_id product_id amount(of products with product_id) — terence6

Mervyn Mervyn · Accepted Answer · 2010-06-09T15:44:44

The following added to your SELECT should do it.

[Product].[price] * [Products on invoice].[number of products on invoice] AS Total

If you include the fields and table names, I can give you a much more accurate statement.

Edit:

SELECT 
  invoice.invoice_id, 
  product.price, 
  products_on_invoice.amount, 
  product.price * products_on_invoice.amount AS Total 
FROM 
  invoice INNER JOIN 
    (products_on_invoice INNER JOIN 
      product 
    ON products_on_invoice.product_id = product.product_id) 
  ON invoice.invoice_id = products_on_invoice.invoice_id

Calculate value from two field in third field

1 Answers