wolf,
i rarely get into people's business operations. i'm making an exception here, just to make some observations.
your change of the products table is very limiting and could destroy a potential audit trail. you are limiting yourself to 1 cost/product as well as 1 invoice/purchase per product.
costing is much more complex than i will get into here. but if you purchase 100 units of product A, it becomes a hot seller, and you want to repurchase it, what happens then? and then lets say the cost has gone up by 12%? how would you keep track of that? your only solution would be to create a new product with the new invoice. but what happens if you then want to find out how many of product A you sold? you have 2 product numbers that you need to keep track of as they are the same product.
perhaps this situation does not apply to you, but maintaining separate tables for vendor purchases is a much better way to handle these things. and then add the product cost to the order_products table. just my 2 cents.
the problem with the sql statement the way it is currently written is two-fold: products_id has no significance, and there is no accounting for the quantity sold on each order. if i wanted to just find out the cost for each invoice of products sold from that invoice, i would use:
Code:
SELECT p.products_invoice, sum(p.products_cost *op.products_quantity), sum(p.products_cost), count(op.products_quantity), sum(op.products_quantity)
FROM orders_products op
left join products p on op.products_id = p.products_id
where op.products_quantity>0
GROUP BY p.products_invoice
i have included a couple of other numbers to show the potential problems if run as you had constructed. the key number is the sum(p.products_cost *op.products_quantity).
if i wanted to see the cost of each item that has a sale for every invoice, i would modify the sql as such:
Code:
SELECT p.products_invoice, p.products_id, sum(p.products_cost *op.products_quantity), sum(p.products_cost), count(op.products_quantity), sum(op.products_quantity)
FROM orders_products op
left join products p on op.products_id = p.products_id
where op.products_quantity>0
GROUP BY p.products_invoice, p.products_id
best.
Bookmarks