Running Totals

If I have a table that contains the following values :
Id Amount
A1 100
B1 50
C1 75
Is it possible to write a query that would output the following :
A1 100 100
B1 50 150
C1 75 225
in other words, add the value of a column in the current row to the value
of a column in the previous row ?
Thanks
Steve
On Sat, 12 Jun 2004 11:06:33 +0100, Steve W wrote:

>If I have a table that contains the following values :
>Id Amount
>A1 100
>B1 50
>C1 75
>Is it possible to write a query that would output the following :
>A1 100 100
>B1 50 150
>C1 75 225
>in other words, add the value of a column in the current row to the value
>of a column in the previous row ?
>Thanks
>Steve
>
Hi Steve,
You'll have to define "previous" row yourself. A table in a relational
database has no fixed ordering. Assuming you want the running total based
on alphabetic ordering by the value if ID, use something like:
SELECT a.Id, a.Amount,
(SELECT SUM(b.Amount)
FROM MyTable AS b
WHERE b.Id <= a.Id) AS RunningTotal
FROM MyTable AS a
ORDER BY a.Id
(untested)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Steve,
You can try this:
use db_dbname
go
select top 10 identity(int,1,1) as num_line, a.value1 as v1, a.value2 as v2
into #temp
from table_name a
where <condition>
go
declare @.valor as float -- or decimal
set @.valor = 0
update #temp
set @.valor = @.valor + v1, v2 = @.valor
go -- the result here...
select * from #temp
go
drop table #temp
Hope this help
Rogrio
Brazil
"Steve W" <lsl@.btconnect.com> escreveu na mensagem
news:%23HfChVGUEHA.2724@.TK2MSFTNGP11.phx.gbl...
> If I have a table that contains the following values :
> Id Amount
> A1 100
> B1 50
> C1 75
> Is it possible to write a query that would output the following :
> A1 100 100
> B1 50 150
> C1 75 225
> in other words, add the value of a column in the current row to the value
> of a column in the previous row ?
> Thanks
> Steve
>