0

I am using PostgreSQL and I have the following tables:

  1. Product

    ID | Name | Price
    
  2. Receipts

    ID | Month | Quantity | Product_ID (FK)
    
  3. 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!

  • Is it product.id or product.id_product? The code does not match the description. And why don't you have the same (product_id) name for the column everywhere? – ypercubeᵀᴹ Jul 17 '14 at 11:26
1

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 the SUM() 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:

SELECT 
    m.month, 
    p.name, 
    COALESCE(r.receipts_quantity, 0) AS receipts_quantity, 
    COALESCE(s.sales_quantity, 0)    AS sales_quantity
FROM 
    product AS p
  CROSS JOIN
    ( VALUES (1),(2),(3),(4),(5),(6) ) AS m(month)     -- months wanted in the report
  LEFT OUTER JOIN 
    ( SELECT month, product_id, SUM(quantity) AS receipts_quantity
      FROM receipts
      GROUP BY month, product_id
    ) AS r
    ON  p.id_product = r.product_id
    AND m.month = r.month
  LEFT OUTER JOIN  
    ( SELECT month, product_id, SUM(quantity) AS sales_quantity
      FROM sales
      GROUP BY month, product_id
    ) AS s
    ON  p.id_product = s.product_id
    AND m.month = s.month ;

If you want to avoid having results for products that did not have any activity in a month, you can add the condition:

WHERE r.month IS NOT NULL 
   OR s.month IS NOT NULL
  • Thanks, ypercube, apparently it works. The only thing is that on the result, when I set VALUES (1),(2),(3),(4),(5),(6) for months, there are rows even for the months when there is no correspondent receipts or sales registered. How can I avoid that? In fact, could you explain a little more why the CROSS JOIN is necessary? – Larissa Leite Jul 17 '14 at 11:36
  • Worked just fine :) Thank you very much!!! – Larissa Leite Jul 17 '14 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.