How to combine two sql queries?

Question

I have a stock table, and I would like to create a report that will show how often items are 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 combine these two queries together.

A request that prints the remaining stock:

SELECT stock.item_id, Sum(stock.pcs) AS stock_balance
FROM stock
GROUP BY stock.item_id;

A query that 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 some JOIN will do the job, but I don't know how to glue the queries together.

Required Conclusion:

item_id | stock_balance | pcs_ordered | number_of_orders
apples  |             0 |         150 |                2
oranges |            10 |          50 |                1
pears   |           100 |         100 |                1

This is just an example. Maybe I will need to add more conditions because there are more columns. Is there a universal technique for combining multiple queries together?

Answer 1

SELECT a.item_id, a.stock_balance, b.pcs_ordered, b.number_of_orders
FROM
    (SELECT stock.item_id, Sum(stock.pcs) AS stock_balance 
    FROM stock 
    GROUP BY stock.item_id) a
LEFT OUTER JOIN
    (SELECT stock.item_id, Sum(stock.pcs) AS pcs_ordered, 
            Count(stock.item_id) AS number_of_orders 
    FROM stock
    WHERE stock.operation = "order"
    GROUP BY stock.item_id) b
ON a.item_id = b.item_id

Answer 2

SELECT
    stock.item_id, 
    Sum(stock.pcs) AS stock_balance,
    pcs_ordered,
    number_of_orders
FROM stock LEFT OUTER JOIN (
    SELECT stock.item_id,
    SUM(stock.pcs) AS pcs_ordered,
    COUNT(stock.item_id) AS number_of_orders
    FROM stock
    WHERE stock.operation ='order'
    GROUP BY stock.item_id
    ) s2 ON stock.item_id = s2.item_id
GROUP BY 
    stock.item_id,
    pcs_ordered,
    number_of_orders