"papaja" <milan.letic@gmail.com> wrote in
news:1139699840.962648.269020@o13g2000cwo.googlegr oups.com:
[color=blue]
> 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?[/color]
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
--
Hope this helps.
Chris.
-------------
C.R. Timmons Consulting, Inc.
http://www.crtimmonsinc.com/ 
