Hi Guys,
I have a stock table and I would like to create a report that will show how often were items ordered.
"stock" table:
item_id | pcs | operation
apples | 100 | order
oranges | 50 | order
apples | -100 | delivery
pears | 100 | order
oranges | -40 | delivery
apples | 50 | order
apples | 50 | delivery
Basically I need to join these two queries together.
A query which prints stock balances:
SELECT stock.item_id, Sum(stock.pcs) AS stock_balance
FROM stock
GROUP BY stock.item_id;
A query which prints sales statistics
SELECT stock.item_id, Sum(stock.pcs) AS pcs_ordered, Count(stock.item_id) AS number_of_orders
FROM stock
GROUP BY stock.item_id, stock.operation
HAVING stock.operation="order";
I think that some sort of JOIN would do the job but I have no idea how to glue queries together.
Desired output:
item_id | stock_balance | pcs_ordered | number_of_orders
apples | 0 | 150 | 2
oranges | 10 | 50 | 1
pears | 100 | 100 | 1
This is just example. Maybe, I will need to add more conditions because there is more columns. Is there a universal technique of combining multiple queries together?
