Display balance as a column on each row

Hello,

I have a table (Transaction) which has the field amount (which can be either negative or positive), each transaction has a date and transaction_id.

I would like to display all the transactions with the balance (up to the transaction date) as a separate column calculated on the fly (there is no balance column in the table).

Ex:

Transaction_id------Date----Amount----Balance_up_to_transaction

1 ----------------28-02-2009 – 55 ------ 55

2 ----------------8-03-2009 ---- -33 ------ 22

3----------------9-04-2009 ----- 15 ------- 27

How can this be done using SQL ? The problem is that the balance is Dependant on the sum of all the amounts prior to the transaction ?

Many Thanks.

anyone?

why does it have to be done with sql?

this is a good example of a situation where you should probably do the running balance in your application language as you print the rows retrieved from the database

because my display includes paging and I will lose the current balance on the next page :slight_smile:

that depends on your paging algorithm, doesn’t it :slight_smile:

What if I jump from the 1st page to the third page ? I will lose the balance which is supposed to be calculated according to the sum in the previous rows .

A second questions: I didn’t save the balance in the database, Because I heard that it is not generally a good practice to save “calculated field” , is it true?

yes it’s true

here you go, the sql solution –


SELECT this.Transaction_id
     , this.Date
     , this.Amount
     , ( SELECT SUM(Amount)
           FROM daTable
          WHERE Date < this.Date
             OR Date = this.Date
            AND Transaction_id < this.Transaction_id
       ) AS Balance_up_to_transaction
  FROM daTable AS this
ORDER
    BY this.Date
     , this.Transaction_id

Thank you :slight_smile: