Something like cumulative or running sum

[papaja]

Hello,
My table has 2 columns with some numbers:

col1 col2
---------------
5 0
0 5
0 10
2 0
8 0

I need a query for web page that will produce dataset with 5 columns:

col1 col2 cumulativeSumCol1
cumulativeSumCol2 diff
-------------------------------------------------------------------------------------
5 0 5
0 5
0 5 5
5 0
0 10 5
15 -10
2 0 7
15 -8
8 0 15
15 0
....

cumulativeSumCol1 holds in each row sum of all values from col1's
previous rows. Example: in Row no. 5 cumulativeSumCol1 holds sum of
first 4 rows from col1. This is the same for cumulativeSumCol2.

Diff is difference between cumulativeSumCol1& cumulativeSumCol2.

How to acomplish this?

 

[Chris R. Timmons]

Hello,
My table has 2 columns with some numbers:

col1 col2
---------------
5 0
0 5
0 10
2 0
8 0

I need a query for web page that will produce dataset with 5
columns:

col1 col2 cumulativeSumCol1
cumulativeSumCol2 diff
-----------------------------------------------------------------
-------------------- 5 0 5
0 5
0 5 5
5 0
0 10 5
15 -10
2 0 7
15 -8
8 0 15
15 0
...

cumulativeSumCol1 holds in each row sum of all values from
col1's previous rows. Example: in Row no. 5 cumulativeSumCol1
holds sum of first 4 rows from col1. This is the same for
cumulativeSumCol2.

Diff is difference between cumulativeSumCol1& cumulativeSumCol2.

How to acomplish this?

papaja,

This can be done in SQL if the original data has some kind of order,
like an entry date. In that case, one SELECT statement can
generate the results you want.

For more information on how to calculate statistics like this
in SQL, get Joe Celko's book "SQL For Smarties". The code below
was adapted from section 23.5.1 - "Running Totals".

Copy this code into the query window of either SQL Server 2000 or 2005
and run it:


DECLARE @numbers TABLE
(
entry_date DATETIME,
col1 INT,
col2 INT
)

INSERT INTO @numbers (entry_date, col1, col2) VALUES ('1/1/2006', 5, 0)
INSERT INTO @numbers (entry_date, col1, col2) VALUES ('1/2/2006', 0, 5)
INSERT INTO @numbers (entry_date, col1, col2) VALUES ('1/3/2006', 0, 10)
INSERT INTO @numbers (entry_date, col1, col2) VALUES ('1/4/2006', 2, 0)
INSERT INTO @numbers (entry_date, col1, col2) VALUES ('1/5/2006', 8, 0)

SELECT N1.col1, N1.col2,
(SELECT SUM(N2.col1)
FROM @numbers N2
WHERE N2.entry_date <= N1.entry_date) AS 'cumulativeSumCol1',
(SELECT SUM(N2.col2)
FROM @numbers N2
WHERE N2.entry_date <= N1.entry_date) AS 'cumulativeSumCol2',
((SELECT SUM(N2.col1)
FROM @numbers N2
WHERE N2.entry_date <= N1.entry_date) -
(SELECT SUM(N2.col2)
FROM @numbers N2
WHERE N2.entry_date <= N1.entry_date)) AS 'diff'
FROM @numbers N1
ORDER BY entry_date

 

[papaja]

What are N1 and N2?

I'm working with Microsoft Access as database, I'll try this on Access,
but what are N1 and N2?

 

[Chris R. Timmons]

What are N1 and N2?

I'm working with Microsoft Access as database, I'll try this on
Access, but what are N1 and N2?

papaja,

N1 and N2 are aliases for the @numbers table.

Chris.