Cumulative (running totals)

Hi There,

I have a query witch gives me the following result:

Period NumberOfItems
1 13
2 2
3 1
4 1
5 1
6 0
7 1
8 2
9 1
10 6
11 0
12 2
13 0

But now i want the number of items to be cumulative aswell so like this:

Period NumberOfItems Cumulative
1 13 13
2 2 15
3 1 16
4 1 17
5 1 18
6 0 18
7 1 19
8 2 21
9 1 22
10 6 28
11 0 28
12 2 30
13 0 30

How can i do this?

Thnx in advance,
Victor

Hi Victor,

For a cumulative column you could use a sub query.

For a sub query the SQL would be something like:-

SELECT Period, NumberOfItems,
(SELECT Sum(NumberOfItems) FROM MyTable T2
WHERE T2.Period <= T1.Period) As Cumulative
FROM MyTable T1

T1 and T2 are aliases for the table you are using.

It will only work where you have a column with unique entries such as your
'Period' Column.
If the original table has years as well as periods you will need to add a
WHERE clause to the main query and extend the WHERE clause on the sub query.
e.g.

SELECT Period, NumberOfItems,
(SELECT Sum(NumberOfItems) FROM MyTable T2
WHERE T2.Period <= T1.Period AND T2.Year=2004) As Cumulative
FROM MyTable T1
WHERE T1.Year=2004

Cheers,
Peter