SQL query: SUM values up to the current record

Question

Basically, having this table:

12.10
 2.35
21.45
35.26

I want to, in each record, calculate the sum of all previous records, like this:

12.10 | 12.10
 2.35 | 14.45
21.45 | 35.90
35.26 | 71.16

Answer 1

Assuming that you have two columns, a primary key called id and a column called value then you can use this:

SELECT T1.id, SUM(T2.value)
FROM table1 T1
JOIN table1 T2
ON T2.id <= T1.id
GROUP BY T1.id

If you don't have any unique identifier (why not?) then you can use ROW_NUMBER to create one.

Answer 2

This is called a running total.

If you have a datetime column you can use something like this:

SELECT t1.id, t1.transactiondatetime, amount,
(
 SELECT SUM(amount) 
 FROM dbo.table1 as t1 
 WHERE t1.transactiondatetime <= t0.transactiondatetime
) AS balance
FROM dbo.table1 AS t0