CODEWITHSUNDEEP
mysqlsqljoin
trapper
trapper

Reputation: 11993

Restricting results included in SUM() with the condition in joined table?

I am trying to list my product table but with a link to my delivery orders to give me sums of stock in certain statuses; allocations, dispatched etc

There is a 'status' field to define where an delivery is currently at eg; 1 = allocated, 2 = dispatched, 3 = delivered etc

This is as far as I have got so far

SELECT product.*, sum(quantity) AS allocated FROM product 
    LEFT JOIN delivery_product ON ( product.id = product_id ) 
    LEFT JOIN delivery ON ( delivery_id = delivery.id AND delivery.status = 1 ) 
GROUP BY code           
ORDER BY code

It includes all quantity matches from delivery_product whereas I only want to include those where the delivery order status = 1. I think this is because i am joining to the delivery_product table first before the status restriction kicks in but how else can I do this?

Bonus points if you can see a much better way to approach my task altogether :)

-

product
id  code   stock
1   ABC    1000
2   DEF    2000

delivery
id  status   date   etc
1   1        etc    etc

delivery-product
id  delivery_id   product_id   quantity
1   1             1            500


results
product.id product.code product.stock allocated
1          ABC          1000          500
2          DEF          2000          0

Upvotes: 1

Views: 76

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726609

I think a small modification to your query should work:

SELECT product.*
,   sum(case when delivery.status = 1 then quantity else 0 end) AS allocated
FROM product 
    LEFT JOIN delivery_product ON ( product.id = product_id ) 
    LEFT JOIN delivery ON ( delivery_id = delivery.id AND delivery.status = 1 ) 
GROUP BY code           
ORDER BY code

You could avoid the conditional by converting the delivery join to inner from left outer, but the query would not return rows with quantity of zero if delivery records with the status of 1 are missing for the product.

Upvotes: 1

Related Questions