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!
Best Answer
The query is joining 2 tables (receipts, sales) that both have a many-to-one relationship with
product
. This creates a kind of cartesian (cross) product and will give wrong results in theSUM()
calculations. To avoid that, you need to do the summations for the two tables in two different subqueries to avoid errors.Something like this will work:
If you want to avoid having results for products that did not have any activity in a month, you can add the condition: