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).
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
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?
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