[SOLVED] MySQL User Defined Variable for every spesific ID

MySQL User Defined Variable for every spesific ID

i have create a table and insert some dummy data with this query:

  CREATE TABLE transaction (
  idtransaction INT NOT NULL AUTO_INCREMENT,
  idproduct INT NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY (idtransaction));  

INSERT INTO transaction (idproduct, quantity) VALUES 
(1, 100),
(1, 200),
(2, 120),
(3, 300),
(2, 200),
(2, 200),
(1, 300);

and i want to get a table result like this:

|idtransaction| idproduct | quantity | saldo
    1               1         100       100  
    2               1         100       200
    3               2         120       120   
    4               3         300       300
    5               2         200       320
    6               2         200       520
    7               1         300       500

i have create a query like this:

SELECT  idtransaction,
        idproduct, 
        quantity, 
       (@saldo := @saldo + quantity) as saldo 
FROM    transaction
LEFT JOIN
    (select @saldo :=0) as s on 1=1;

but, the result of saldo history is calculate for all idproduct.

how to get 'saldo' history for every idproduct?

Solution

You need to order the data by idproduct, and then reset @saldo whenever idproduct changes.

If you want the final result ordered by idtransaction, put the query with the running total in a sub-query, and apply the new order to that.

SELECT *
FROM (
    SELECT idtransaction, idproduct, quantity,
            @saldo := IF(idproduct = @lastproduct, @saldo + quantity, quantity) AS saldo,
            @lastproduct := idproduct
    FROM (SELECT *
          FROM transaction
          ORDER BY idproduct, idtransaction) AS t
    CROSS JOIN (SELECT @saldo := 0, @lastproduct = NULL) AS vars) AS x
ORDER BY idtransaction

DEMO