Mysql subquery does not give correct result-CodePudding

I have a mysql table named stock and below query gives totol stock for item_id = 271.

select sum(qty) from stock where item_id = 271;
 ---------- 
| sum(qty) |
 ---------- 
|   127.00 |
 ---------- 
1 row in set (0.001 sec)

But in my original query I can not get the correct stock value for that item_id.

This is my qury:

SELECT p.purchase_id
    , p.reference_number
    , pi.item_id
    , pi.received_qty
    , pi.cost_price_before_disc
    , pi.item_discount
    , round(pi.received_qty * pi.cost_price_after_disc, 2) as inline_total
    , sum(current_stock) as stock
FROM purchase_draft p 
  JOIN purchase_item_draft pi USING(purchase_id)
  JOIN item i ON pi.item_id = i.item_id
  LEFT JOIN (
              SELECT p.purchase_id
                   , pi.item_id
                   , sum(ifnull(s.qty, 0)) as current_stock
              FROM purchase_draft p 
                LEFT JOIN purchase_item_draft pi USING(purchase_id)
                LEFT JOIN stock s ON s.item_id = pi.item_id ANd qty_type = 'a'
              GROUP BY pi.item_id, p.purchase_id 
            ) s ON s.item_id = pi.item_id 
WHERE p.purchase_id = 4
GROUP BY purchase_id, pi.item_id

 ------------- ------------------ --------- -------------- ------------------------ --------------- -------------- -------- 
| purchase_id | reference_number | item_id | received_qty | cost_price_before_disc | item_discount | inline_total | stock  |
 ------------- ------------------ --------- -------------- ------------------------ --------------- -------------- -------- 
|           4 | PO2022/0004      |      92 |           60 |                1285.00 |          0.00 |     77100.00 |   0.00 |
|           4 | PO2022/0004      |     271 |           40 |                3396.00 |          0.05 |    135838.00 | 254.00 |
|           4 | PO2022/0004      |     407 |           10 |                3100.00 |        500.00 |     26000.00 |   0.00 |
|           4 | PO2022/0004      |     582 |           30 |                2898.00 |          0.21 |     86933.70 |   0.00 |
|           4 | PO2022/0004      |     583 |           20 |                1552.50 |          1.33 |     31023.40 |   0.00 |
|           4 | PO2022/0004      |     640 |           15 |                3285.00 |          2.00 |     49245.00 |   0.00 |
 ------------- ------------------ --------- -------------- ------------------------ --------------- -------------- -------- 
6 rows in set (0.002 sec)

Can I know what I have done wrong in my query?

CodePudding user response：

You need to aggregate the stock table in the join.

Replace this:

LEFT JOIN stock s ON s.item_id = pi.item_id ANd qty_type = 'a'

With:

LEFT JOIN (
    SELECT item_id, sum(qty) AS qty
    FROM stock
    WHERE qty_type = 'a'
    GROUP BY item_id
) s ON s.item_id = pi.item_id