Difference between two values used to find running total

Question

I have one table called "GOLDSTOCKTABLE"

+----------+-------------+----------------+  
!ID ! GRAM !  GRAMTOBANK ! LOCKERBALANCE  !
+----------+-------------+----------------+ 
!1  ! 10   !    9        !                !
!2  ! 15   !    10       !                ! 
!3  ! 8    !    8        !                ! 
!4  ! 45   !    40       !                !
+----------+-------------+----------------+

On each entry of record ,the difference between GRAM and GRAMTOBANK stored with the cumulative sum as in LOCKERBALANCE,The resultant table look like

+----------+-------------+----------------+  
!ID ! GRAM !  GRAMTOBANK ! LOCKERBALANCE  !
+----------+-------------+----------------+ 
!1  ! 10   !    9        !    1           !
!2  ! 15   !    10       !    6           ! 
!3  ! 8    !    8        !    6           ! 
!4  ! 45   !    40       !    11          !
+----------+-------------+----------------+

Possible duplicate of [Create a Cumulative Sum Column in MySQL](http://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql) — Theo, Feb 18 '17 at 17:46
@TheoTonge Here the difference between GRAM and GRAMTOBANK is used for the calculation of running total — Ajith, Feb 18 '17 at 17:50
OK yes they are slightly different - have added an answer instead. — Theo, Feb 18 '17 at 17:56

McNets · Accepted Answer · 2017-02-18T18:13:37.140

1

Use a variable to store cumulative sum:

select ID, GRAM, GRAMTOBANK
       , @lb := @lb + (GRAM - GRAMTOBANK) as LOCKERBALANCE  
from
    (select @lb := 0) x,
    (select ID, GRAM, GRAMTOBANK
     from GOLDSTOCKTABLE
     order by ID) y

edited Feb 18 '17 at 18:13

answered Feb 18 '17 at 17:48

McNets

10,352
3
32
61

@Mc Nets Can we use the exact query with the php script,Does variables works perfectly? – Ajith Feb 18 '17 at 18:27
But it should works as some other script. @AjithVManali – McNets Feb 18 '17 at 18:36

Theo · Answer 2 · 2017-02-18T18:15:43.210

0

You can do this using variables - note you must run the first query to set the variable to 0 before you then run the main query.

SET @LOCKERBALANCE=0;

SELECT 
  GOLDSTOCKTABLE.*, 
  (@LOCKERBALANCE := @LOCKERBALANCE + (GRAM-GRAMTOBANK)) as LOCKERBALANCE
FROM GOLDSTOCKTABLE
ORDER BY ID

edited Feb 18 '17 at 18:15

answered Feb 18 '17 at 17:55

Theo

1,608
1
9
16

Can you paste the CREATE TABLE statement for GOLDSTOCKTABLE and confirm your mysql version? – Theo Feb 18 '17 at 18:12
Actually this is a table with 26 columns,For understand easily i shrink the table with 4 columns,In which version the code works? – Ajith Feb 18 '17 at 18:31
The number of columns should not change the result, I have just tested with an additional 50 columns on a test table and it still works. The main reason for seeing the CREATE TABLE statement would be to see the exact data types used for each column. – Theo Feb 18 '17 at 18:38
1 GLID PrimaryIndex int(5) 2 GRAMTOBANK int(5) 3 GRAMTOBANK int(5) 4 LOCKERBALANCE int(10) – Ajith Feb 18 '17 at 18:58
I am a bit confused why this would not work - are you running directly on the mysql client? – Theo Feb 18 '17 at 19:05
on XAMP's PhpMyadmin – Ajith Feb 18 '17 at 19:12
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/136045/discussion-between-theo-tonge-and-ajith-v-manali). – Theo Feb 18 '17 at 19:40

Difference between two values used to find running total

2 Answers2