dimanche 21 février 2016

Sql Count Where Groupby SubQuery

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