How To Combine Two Sql Queries?

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 | 5

Solution 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

Solution 2:

This should do it

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