I expect that this script will not output 0 values, but it does. I think that due to and (select sum(lot_movement.quantity) FROM lot_movement where lot_movement.lot_id = lot.id) > 0 0 value will not output. What I do wrong?

select lot.*, sum(movement.quantity) as value 
from lot
left join lot_movement as movement on lot.id = movement.lot_id
where lot.item_id = 8 and movement.storage_id = 3 and 
(select sum(lot_movement.quantity) 
  FROM lot_movement 
  where lot_movement.lot_id = lot.id) > 0
group by lot.id;

I tried to add 'and sum(lot_movement.quantity) > 0', but this makes problem 'invalide use of group function'

lots in database

lot_movements in database

output with 0 values

I see that 'and (select sum(lot_movement.quantity) FROM lot_movement where lot_movement.lot_id = lot.id group by lot_movement.lot_id) > 0' is redundent. It doesn't makes effect on result. So how I can privent 0 values output?

CodePudding user response：

Your query doesn't give the expected result because you're filtering by lot.item_id = 8 and movement.storage_id = 3 in the where clause, but you're not applying that same filtering in the subselect.

I'm not exactly sure what you're trying to achieve, but I suspect adding a having clause instead of the subselect solves your problem:

select lot.id, sum(movement.quantity) as value 
from lot
left join lot_movement as movement on lot.id = movement.lot_id
where lot.item_id = 8 and movement.storage_id = 3
group by lot.id
having sum(movement.quantity) > 0