2
\$\begingroup\$

I have a query that outputs a list of products with some fields from other tables. That query works fine.

Now, I'm attempting to retrieve the available stock for each product in that list. I'm not sure if I'm doing it correctly or in the most performant way. Here's what I've got working so far.

select distinct 
p.id, 
p.name
if(coalesce(x.in - x.out, 0) = 0, 0, 1) as quantity,
[other fields from joined tables]
from products p 
[a bunch of inner joins]
left join ( 
    select 
    s.id_products, 
    sum(case when s.type = 'in' then s.quantity else 0 end) as 'in', 
    sum(case when s.type = 'out' then s.quantity else 0 end) as 'out' 
    from stock s 
    group by s.id_products 
) x on x.id_products = p.id_products 
group by id 

EDIT: I made a mistake in the query the if statement in the select is just for a flag display. Please replace the following.

if(coalesce(x.in - x.out, 0) = 0, 0, 1) as quantity,
(x.in - x.out, 0) as quantity,
\$\endgroup\$
1
1
\$\begingroup\$

Discarded sums

You calculate these sums:

sum(case when s.type = 'in' then s.quantity else 0 end) as 'in', 
sum(case when s.type = 'out' then s.quantity else 0 end) as 'out' 

but then reduce them to a 0/1 here:

if(coalesce(x.in - x.out, 0) = 0, 0, 1) as quantity,

This means that a sum is not appropriate at all. This should be refactored to an if exists, where the predicate compares s.type to in/out and checks that s.quantity > 0.

\$\endgroup\$
1
  • \$\begingroup\$ Hello @Reinderien, I accidentally leave that...but is just for a flag display. I will now add an edit to my post \$\endgroup\$ – Matías Cánepa Jun 23 '20 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.