-1

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Daniil Shipilov
  • 1
  • 2
  • 3
    Table definitions, sample data and expected outcome as text would be helpful. NB group by on one column whilst selecting all columns is unlikely to be helpful. – P.Salmon Apr 03 '22 at 14:02
  • 2
    https://stackoverflow.com/help/how-to-ask – P.Salmon Apr 03 '22 at 14:05
  • Change `lot.*` to `lot.id` share sample data. This was addressed in earlier comment, do not repeat question that you typed in the question when asking a question. – Luuk Apr 03 '22 at 14:08
  • 1
    @DaniilShipilov: But it will prevent an error like "invalide use of group function" – Luuk Apr 03 '22 at 14:24
  • 1
    The query in the link is not the same as the query published. Do take some time to make the question consistent. AND links are not useable whereas text is. – P.Salmon Apr 03 '22 at 14:27
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. Please in code questions give a [mre]. [ask] [Help] Please clarify via edits, not comments. PS Please: Backquote `\``is not single quote `'`. Look at the formatted version of your post before you publish. Spell check. Format code reasonably. Write a specific title. – philipxy Apr 03 '22 at 21:40
  • Please in code questions give a [mre]--cut & paste & runnable code & example input; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) – philipxy Apr 03 '22 at 21:56
  • Possible duplicate of [Left Join With Where Clause](https://stackoverflow.com/q/4752455/3404097) – philipxy Apr 04 '22 at 01:23

1 Answers1

0

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
Marleen
  • 2,245
  • 2
  • 13
  • 23