expected that this query will not output 0 values, but it does [closed]

I expected that this query will not output 0 values, but it does. I thought that and (...) > 0 will not output 0 values. So how I can prevent the output of 0 values?

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 gives error invalid 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 redundant. It doesn’t affect the result.

Answer

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