March 3, 2006 at 8:21 am
I have seen this done several different ways but i thought i would post it anyway to see if anyone has a better way to do it.
I want to generate a rolling sum by ID for the following result set:
ID Value
--- ------
16 1
16 8
16 1
16 7
17 1
17 1
17 2
17 2
18 1
18 2
19 2
19 1
19 3
The result set show include an aggregate that displays as the following:
ID Value SumValue
--- ------ --------
16 1 1
16 8 9
16 1 10
16 7 17
17 1 1
17 1 2
17 2 4
17 2 6
18 1 1
18 2 3
19 2 2
19 1 3
19 3 6
I can do this by looping through a cursor, calculating the aggregate and generating a new temp table, can anyone show me a simple way to do this within one (or close to one) query?
March 3, 2006 at 8:51 am
Is there a 3rd column available in the original 2 column set that supplies the order in which the rows occur ?
March 3, 2006 at 9:01 am
There is an identity column... it would be like:
Identity ID Value
------- --- ------
5678 16 1
5679 16 8
5680 16 1
5681 16 7
5682 17 1
5683 17 1
5684 17 2
5685 17 2
5686 18 1
5687 18 2
5688 19 2
5689 19 1
5690 19 3
March 3, 2006 at 9:12 am
Select t1.ID, t1.Value,
(Select Sum(t2.Value) From YourTable As t2
Where t2.ID = t1.ID And t2.Identity <= t1.Identity
) As Summed
From YourTable As t1
March 3, 2006 at 9:35 am
Perfect! Thanks again!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply