Recursive function in SQL

Question

i have the data in my table as follows,

+----+-----+
| ID | Qty |
+----+-----+
|  1 | 100 |
|  2 | 200 |
|  3 | 150 |
|  4 |  50 |
+----+-----+

i need the result as follows,

+----+-----+-------+
| ID | Qty | C.Qty |
+----+-----+-------+
|  1 | 100 |   100 |
|  2 | 200 |   300 |
|  3 | 150 |   450 |
|  4 |  50 |   500 |
+----+-----+-------+

the result of third column will be the sum of previous rows, please any one help....

It' already been answered [here](http://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum) — remigio, Nov 15 '13 at 06:36
Oh...he meant previous *rows*, not columns. I'll correct it. — Paul Draper, Nov 15 '13 at 06:37

score 2 · Answer 1 · answered Nov 15 '13 at 06:34

2

I would just use a subquery:

SELECT ID, Qty,
   (SELECT SUM(Qty) FROM [My Table] b WHERE b.ID <= [My Table].ID) AS [Total Qty]
FROM [My Table]

answered Nov 15 '13 at 06:34

David

34,223
3
62
80

score 2 · Answer 2 · answered Nov 15 '13 at 06:34

2

Please try:

SELECT S1.ID, S1.Qty ,sum(S2.Qty) CUM_SUM
FROM YourTable S1 join YourTable S2
on S1.ID>=S2.ID
group by S1.ID, S1.Qty
ORDER BY S1.ID

answered Nov 15 '13 at 06:34

TechDo

18,398
3
51
64

score 1 · Answer 3 · answered Nov 15 '13 at 06:34

1

 SELECT ID, Qty,
 SUM(Qty) OVER(ORDER BY ID 
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
      AS C.QTY
 FROM Table

answered Nov 15 '13 at 06:34

Linga

10,379
10
52
104

Looks interesting. What database server is this for? – David Nov 15 '13 at 06:36
@David Sql Server 2012 (not supported prior). Not sure when oracle started supporting it. Works in at least 11g. Wouldn't be surprised if it worked postgresql. Definitely won't work in MySQL – Conrad Frix Nov 15 '13 at 06:42

score 0 · Answer 4 · edited Nov 15 '13 at 07:04

0

try this:

select a.id,a.qty,sum(b.qty) as total_qty
from table a cross join table b
where b.id <= a.id
group by a.id,a.qty 
order by a.id

demo

edited Nov 15 '13 at 07:04

rolfl

17,539
7
42
76

answered Nov 15 '13 at 06:47

vhadalgi

7,027
6
38
67

Recursive function in SQL

4 Answers4