Creating a cumulative sum with an SQL database using just one Query.

To create a cumulative sum of a table field, you can set up a Query with SQL’s Inner Join command.

Example:

        Date/Hour                     Field                 Accumulated
20/02/2014 09:54:05                1                              1
20/02/2014 09:54:15                3                              4
20/02/2014 09:54:35                5                              9
20/02/2014 10:03:58                1                             10

To calculate the accumulated value, you must set up two variables for the same table: one for the table fields and another one for the sum. Even if both variables refer to the same table, the SQL will handle them as two different tables.

In the example below, the t1 variable was set up for the fields, and t2 for the sum.

SELECT t1.E3TimeStamp,t1.Field, SUM(T2.Field) as Sum
FROM Sum AS t1
INNER JOIN Sum AS t2 on t1.E3TimeStamp >= t2.E3TimeStamp
GROUP BY  t1.E3TimeStamp,t1.Field
ORDER BY t1.E3TimeStamp  ASC

The Inner Join function then joins both tables so that the sum will be displayed alongside the records in the main table.