I am using PostgreSQL and I have the following tables:
Product
ID | Name | Price
Receipts
ID | Month | Quantity | Product_ID (FK)
Sales
ID | Month | Quantity | Product_ID (FK)
I am trying to come up with a query that displays something like this:
Month | Product.Name | Amount received | Amount sold
It is very important to keep in mind that there can be more than one receipt or sale of the same product on the same month. I need to come up with a query that tells me the amount received and the amount sold of a particular product on a each month. It is possible that a product is not sold in a month (that's why the OUTER JOIN below).
The best that I've got so far is this:
SELECT s.month, p.name, r.quantity, SUM(s.quantity)
FROM product AS p
INNER JOIN receipts AS r
ON p.id_product = r.product_id
LEFT OUTER JOIN sales AS s
ON p.id_product = v.product_id
GROUP BY s.month, p.name, r.quantity
Let's say in the same month there were 2 receipts of keyboards. The first one with 100 keyboards and the second one with 50 keyboards. Also, there was a sale of 10 keyboards and then another one of 20 keyboards.
An example of what is returned by this query:
Month | Product.Name | Amount received | Amount sold
6 | Keyboard | 100 | 30
6 | Keyboard | 50 | 30
This one works fine when there are mutiple sales (apart from the repetition) but not when there are multiple receipts. I was thinking that I should relate s.month and r.month with an INNER JOIN, but I don't know how.
Sorry for the bunch of information at once. If there is anything unclear, let me know. Can anyone help me out?
Thanks!
product.id
orproduct.id_product
? The code does not match the description. And why don't you have the same (product_id
) name for the column everywhere?