CODEWITHSUNDEEP
phpmysql
Priyamanu
Priyamanu

Reputation: 183

Optimize my Mysql query to get stock status

This is my mysql query:

SELECT 
    (SELECT SUM(jb_qty) FROM tbl_transactions WHERE jb_Type=1 AND jb_s_Id=$s_Id) AS purqty, 
    (SELECT SUM(jb_qty) FROM tbl_transactions WHERE jb_Type=2 AND jb_s_Id=$s_Id) AS salqty
FROM tbl_transactions LIMIT 1

to get the stock status (jb_type 1 is purchase and 2 is sales).

I want the current stock status as well from this (like purqty-salqty) and need a better query to get these results.

Upvotes: 1

Views: 112

Answers (1)

Mureinik
Mureinik

Reputation: 312116

There are a couple of things you could easily improve here. First, you could eliminate the sub-queries and just use a single query with case expressions, so the same rows will only be processed once. Second, since the jb_s_Id condition is the same for both queries, you could move it out to the where clause, again, to reduce the amount of rows the query needs to process:

SELECT SUM(CASE jb_type WHEN 1 THEN jb_qty END) AS purqty,
       SUM(CASE jb_type WHEN 2 THEN jb_qty END) AS salqty
FROM   tbl_transactions
WHERE  jb_s_Id = $s_Id

EDIT:
To answer the question in the comments, if you want to subtract salqty from purqty, you could just use the - operator between both expressions:

SELECT SUM(CASE jb_type WHEN 1 THEN jb_qty END) AS purqty,
       SUM(CASE jb_type WHEN 2 THEN jb_qty END) AS salqty
       SUM(CASE jb_type WHEN 1 THEN jb_qty END) - 
       SUM(CASE jb_type WHEN 2 THEN jb_qty END) AS diff
FROM   tbl_transactions
WHERE  jb_s_Id = $s_Id

A more elegant way, though, would be to use a subquery, so you don't have to repeat the same expressions twice:

SELECT purqty, salqty, purqty - salqty
FROM   (SELECT SUM(CASE jb_type WHEN 1 THEN jb_qty END) AS purqty,
               SUM(CASE jb_type WHEN 2 THEN jb_qty END) AS salqty
        FROM   tbl_transactions
        WHERE  jb_s_Id = $s_Id) t

Upvotes: 3

Related Questions