On 2013-11-18 04:37, Ken Tanzer wrote:
If the tables aren't huge, you're not concerned about optimization, and you just want to get your numbers, I think something like this would do the trick. I haven't actually tried it 'cause I didn't have easy access to your tables:
SELECT
a.product_id,
a.product_name,
b.initial_stock_sum,
c.in_out_sum,
c.in_sum,
c.out_sum
FROM
a
LEFT JOIN
(SELECT
product_id,
SUM(initial_stock) AS initial_stock_sum
FROM b
GROUP BY product_id
) b USING (product_id)
LEFT JOIN
(SELECT
product_id,
sum(CASE WHEN date < 'BEGINNING DATE' THEN in-out ELSE 0 END) AS in_out_sum,
sum(CASE WHEN date BETWEEN 'BEGINNING DATE' AND 'ENDING DATE' THEN in ELSE 0 END) AS in_sum,
sum(CASE WHEN date BETWEEN 'BEGINNING DATE' AND 'ENDING DATE' THEN out ELSE 0 END) AS out_sum
FROM c
GROUP BY product_id
) c USING (product_id)
WHERE a.supplier_id='XXX';
Cheers,
Ken
I'm a big fan of using LATERAL joins (9.3+) for this use case.