query using subquery and joins optimization

Question

I'm somehow not contented with my query statement and am wondering if there are other better way to do this:

SELECT product.product, 
 COALESCE((SELECT SUM(qty) FROM stocktrxn 
 INNER JOIN stock on stock.tra_no = stocktrxn.tra_no 
 WHERE product_no = s1.product_no and stock.is_out = false) - 
 (SELECT SUM(qty) FROM stocktrxn 
 INNER JOIN stock on stock.tra_no = stocktrxn.tra_no 
 WHERE product_no = s1.product_no and stock.is_out = true), 0)
 as qty
FROM stocktrxn s1
RIGHT JOIN product on product.id = s1.product_no
GROUP BY product ASC;

Gordon Linoff · Accepted Answer · 2016-02-10 03:46:05Z

1

I think you just want conditional aggregation. Something like this:

SELECT p.product,
       (SUM(CASE WHEN s.is_out = false THEN qty ELSE 0 END) -
        SUM(CASE WHEN s.is_out = true THEN qty ELSE 0 END) -
       ) as qty
FROM product LEFT JOIN
     stocktrxn st
     ON p.id = st.product_no LEFT JOIN
     stock s
     ON s.tra_no = st.tra_n
GROUP BY p.product ASC;

answered Feb 10, 2016 at 3:46

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

query using subquery and joins optimization

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related