HELP me How do it with SQL ?

[mnavahan]

for example :
i have one table with one field A

A
2
5
0
10

i need get below responce from SELECT :

A B
2 2
5 7
0 7
10 10

this mean i need :
B0 = A0
B1 = A0 + A1
B2 = A0 + A1 + A2
AND ....

HOW DO IT WITH SQL ?

 

[swampBoogie]

You need to have some other column in the table so you can with certainty get the records in that order.

PK A
1 2
2 5
3 0
4 10

in that case

select A , (select sum(A) from t 
   where PK <= t1.PK) from t as t1
order by pk

[/code]

 

[mnavahan]

NOT work :-(

 

[r937]

if you add the additional sequencing column to your table as swampboogie suggested, yes it does work

relational databases do not maintain a sequence of rows in a table

any sequence you want must be explicitly specified in the ORDER BY clause, or implicit in the use of comparison operators like < (less than), which is what swampboogie's query uses

rudy

 

[Jamfool]

I offer the following if its any use to you. Although swampBoogie has offered an easier approach by indexing your data.

Obviously row_name,table_name need to be changed



CREATE TABLE #Temp_Cumulative_Table (row_name numeric, RunningTotal numeric)

DECLARE @row_name numeric,
@RunningTotal numeric
SET @RunningTotal = 0

DECLARE Cumulative_Cur CURSOR
FOR
SELECT row_name
FROM table_name

OPEN Cumulative_Cur

FETCH NEXT FROM Cumulative_Cur INTO @row_name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @RunningTotal = @RunningTotal + @row_name
INSERT #Temp_Cumulative_Table VALUES (@row_name,@RunningTotal)
FETCH NEXT FROM Cumulative_Cur INTO @row_name
END

CLOSE Cumulative_Cur
DEALLOCATE Cumulative_Cur

SELECT * FROM #Temp_Cumulative_Table

DROP TABLE #Temp_Cumulative_Table