Yuli Iswadi Yuli Iswadi - 5 months ago 11
MySQL Question

how to take a value less than 0 if recorded obtained like this?



select c.location_id as id_gudang, c.location_name as nama_gudang, a.item_code as id_barang, a.item_name as nama_barang, sum(b.item_qty) as qty
from t_inventory as a
join t_inventory_transaction as b on a.item_id = b.item_id
join t_site_location as c on b.whse_id = c.location_id
group by a.item_code





this data from query
enter image description here

Answer

If you want that, you can wrap your sql into a subquery like:

select *
from (
    select c.location_id as id_gudang, c.location_name as nama_gudang, a.item_code as id_barang, a.item_name as nama_barang, sum(b.item_qty) as qty
    from t_inventory as a
    join t_inventory_transaction as b on a.item_id = b.item_id
    join t_site_location as c on b.whse_id = c.location_id
    group by a.item_code) t
where qty < 0

Or just use HAVING:

select c.location_id as id_gudang, c.location_name as nama_gudang, a.item_code as id_barang, a.item_name as nama_barang, sum(b.item_qty) as qty
from t_inventory as a
join t_inventory_transaction as b on a.item_id = b.item_id
join t_site_location as c on b.whse_id = c.location_id
group by a.item_code
having qty < 0