Calculate value from two field in third field
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 quer开发者_StackOverflowy 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 ?
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
精彩评论