CODEWITHSUNDEEP
mysqlsql
Bla...
Bla...

Reputation: 7288

Ordering issue when using SQL variable

I run this query:

SELECT stockcarddetail.id, stockcarddetail.date, stockcarddetail.quantity, stockcarddetail.pricePerItem
  FROM Stockcard
  LEFT JOIN staff
         ON staff.branchId = stockcard.branchId
  LEFT JOIN stockcarddetail
         ON stockcarddetail.stockcardId = stockcard.id
 WHERE staff.username = 'jemmy.h'
   AND stockcarddetail.quantity > 0
   AND stockcard.productId = '98924a5f-6afb-11e7-8dd4-2c56dcbcb038'
 ORDER BY date ASC

and get the result below:

id | date       | quantity| pricePerItem
50 | 2017-10-15 | 10.00   | 10000.00
1  | 2017-10-18 | 20.00   | 10000.00

Then, I need to calculate the cumulative of quantity based on the order above, so I run this query: 

SELECT a.*, @tot:=@tot + a.quantity FROM
(SELECT @tot:= 0)b
JOIN
(SELECT stockcarddetail.id, stockcarddetail.date, stockcarddetail.quantity, stockcarddetail.pricePerItem
FROM Stockcard
LEFT JOIN staff
       ON staff.branchId = stockcard.branchId
LEFT JOIN stockcarddetail
       ON stockcarddetail.stockcardId = stockcard.id
WHERE staff.username = 'jemmy.h'
  AND stockcarddetail.quantity > 0
  AND stockcard.productId = '98924a5f-6afb-11e7-8dd4-2c56dcbcb038'
ORDER BY date ASC) a

Then I got this result:

id | date       | quantity| pricePerItem | @tot
1  | 2017-10-18 | 20.00   | 10000.00     | 20
50 | 2017-10-15 | 10.00   | 10000.00     | 30

However, the result that I want is like this:

id | date       | quantity| pricePerItem | @tot
50 | 2017-10-15 | 10.00   | 10000.00     | 10
1  | 2017-10-18 | 20.00   | 10000.00     | 30

How can I get the expected result?

EDIT

Simplified version of the problem can be found here: http://sqlfiddle.com/#!9/f6ad91/3

Upvotes: 0

Views: 64

Answers (2)

Magisch
Magisch

Reputation: 7352

From what I understand from you, you want the cumulative total for each entry.

I suggest ditching the variable and relying on a subquery instead:

SELECT  
scd.id,  
scd.date,  
scd.quantity,  
scd.pricePerItem, 
(SELECT SUM(scd1.quantity) FROM StockcardDetail AS scd1 WHERE scd1.stockcardId = scd.stockcardId AND scd1.date <= scd.date) AS total 
FROM Stockcard 
LEFT JOIN staff ON staff.branchId = stockcard.branchId 
LEFT JOIN stockcarddetail AS scd ON scd.stockcardId = stockcard.id

WHERE staff.username = 'jemmy.h'  
AND scd.quantity > 0  
AND stockcard.productId = '98924a5f-6afb-11e7-8dd4-2c56dcbcb038' 
ORDER BY scd.date ASC

The idea behind this is to make it select the sum of all entries prior (including the current one) for each entry.

Upvotes: 1

Ravi
Ravi

Reputation: 31417

As per my understanding, you should get the expected output from your query. But, you aren't getting your expected output, then other possible solution is (WITHOUT JOIN)

SET @tot:= 0;

SELECT 
stockcarddetail.id, 
stockcarddetail.date, 
stockcarddetail.quantity, 
stockcarddetail.pricePerItem, 
@tot:=@tot + stockcarddetail.quantity as Total
FROM Stockcard
LEFT JOIN staff ON staff.branchId = stockcard.branchId
LEFT JOIN stockcarddetail ON stockcarddetail.stockcardId = stockcard.id
WHERE staff.username = 'jemmy.h' AND stockcarddetail.quantity > 0 AND stockcard.productId = '98924a5f-6afb-11e7-8dd4-2c56dcbcb038'
ORDER BY date ASC

Upvotes: 1

Related Questions