Hey I have this query,
SELECT item_type.id, item_type.item_type,
(SELECT COUNT(*) FROM item WHERE item.sale_transaction_id IS NULL) as stock_qty,
(SELECT COUNT(*) FROM item WHERE item.sale_transaction_id IS NOT NULL) as sold_qty
FROM item
JOIN item_type ON item.item_type_id = item_type.id
GROUP BY item.item_type_id
This gives me a result:
| id | item_type | stock_qty | sold_qty|
----------------------------------------
| 1 | Book | 12 | 12 |
| 2 | Pencil | 12 | 12 |
| ........... # etc
But this does not work as intended, I need to do it like this to make it work:
SELECT item_type.id, item_type.item_type,
COUNT(item.purchase_transaction_id) - COUNT(item.sale_transaction_id) as stock_qty,
COUNT(item.sale_transaction_id) as sold_qty
FROM item
JOIN item_type ON item.item_type_id = item_type.id
GROUP BY item.item_type_id
and the result is what I want and this is the correct/expected output:
| id | item_type | stock_qty | sold_qty|
----------------------------------------
| 1 | Book | 1 | 0 |
| 2 | Pencil | 0 | 5 |
| ........... # etc
In my Table structure, each item that has sale_transaction_id is marked as sold. My question is why the first one is not working as intended? and how do I make it to work as 2nd one? Is it actually possible using subquery for this type of query?
Aucun commentaire:
Enregistrer un commentaire