CODEWITHSUNDEEP
sqlsqlite
andiwin
andiwin

Reputation: 1602

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?

Upvotes: 0

Views: 82

Answers (3)

Mita
Mita

Reputation: 422

Start from "item_type" table, instead of "item" table and use left join, otherwise you will never get a row in the query result if you not have items from a type.

SELECT
    item_type.id,
    item_type.item_type,
    SUM(CASE WHEN item.id IS NOT NULL AND item.sale_transaction_id IS NULL THEN 1 ELSE 0 END) AS stock_qty,
    SUM(CASE WHEN item.id IS NOT NULL AND item.sale_transaction_id IS NOT NULL THEN 1 ELSE 0 END) AS sold_qty
FROM
    item_type
LEFT JOIN
    item
ON
    item.item_type_id = item_type.id
GROUP BY
    item_type.id, item_type.item_type

Avoid using subselects. Each subselect you use will be executed for each row and that will slow down performance a lot. You can run explain on both queries (subselect and join version) and you will see what I mean

It will be helpful if you post an example data of initial tables.

Upvotes: 1

vtuhtan
vtuhtan

Reputation: 1066

SELECT item_type.id, item_type.item_type, 
SUM(case when item.sale_transaction_id IS NULL then 1 else 0 end) as stock_qty,
SUM(case when item.sale_transaction_id IS NOT NULL then 1 else 0  end) as sold_qty
FROM item
JOIN item_type ON item.item_type_id = item_type.id
GROUP BY item_type.id, item_type.item_type

Is this what you need?

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You need to add correlation to the subqueries:

SELECT item_type.id, item_type.item_type, 
    (SELECT COUNT(item.purchase_transaction_id) - COUNT(item.sale_transaction_id)
     FROM item 
     WHERE item.item_type_id = i.item_type_id) as stock_qty,
    (SELECT COUNT(item.sale_transaction_id) 
     FROM item 
     WHERE item.item_type_id = i.item_type_id ) as sold_qty
FROM item AS i
JOIN item_type ON i.item_type_id = item_type.id
GROUP BY i.item_type_id

The subqueries are now correlated: they are executed for each item_type_id of the outer query and return results for this exact value each time.

But this seems like an overkill, since you can get the same result applying aggregation in the outer query, just like you do in the second query of your question.

Upvotes: 1

Related Questions