November 3, 2004 at 7:41 am
I have a problem I was hoping someone could help with.
I need to calculate a cumulative value from one of my tables. The table looks like...
| ID | SecondaryId | Value |
|---|---|---|
| 1 | 1 | 3.2 |
| 1 | 2 | 1.3 |
| 1 | 3 | 0.4 |
| 2 | 1 | 4.8 |
| 2 | 2 | 1.3 |
| 3 | 1 | 1.5 |
| 3 | 2 | 2.3 |
The result I would like to see is an accumulation of the value column for each Id. So, in this example, I would like to see a table like...
| ID | SecondaryId | Value | CumulativeValue |
|---|---|---|---|
| 1 | 1 | 3.2 | 3.2 |
| 1 | 2 | 1.3 | 4.5 |
| 1 | 3 | 0.4 | 4.9 |
| 2 | 1 | 4.8 | 4.8 |
| 2 | 2 | 1.3 | 6.1 |
| 3 | 1 | 1.5 | 1.5 |
| 3 | 2 | 2.3 | 3.8 |
The combination of ID and Secondary Id make up the primary key for the table. I know I could do this with a cursor, but generally I think cursors are bad and would prefer to avoid using one in this case. Any help would be greatly appreciated. Thanks.
George Mastros
Orbit Software, Inc.
November 3, 2004 at 9:46 am
I think this should do it:
SELECT A.ID,
A.SecondaryId,
A.Value,
SUM(B.Value) AS CumulativeValue
FROM YourTable A
JOIN YourTable B ON B.Id <= A.Id
AND ((B.Id = A.Id AND B.SecondaryId <= A.SecondaryId) OR (B.Id < A.Id))
GROUP BY A.ID,
A.SecondaryId,
A.Value
--
Adam Machanic
whoisactive
November 3, 2004 at 9:46 am
select 1 as [ID], 1 as SecondaryID, 3.2 as Value into #temp
union
select 1,2, 1.3
union
select 1, 3, 0.4
union
select 2, 1, 4.8
union
select 2, 2, 1.3
union
select 3, 1, 1.5
union
select 3, 2, 2.3
select id, secondaryID,value, CumulativeValue=value +
ISNULL((select sum(value) from #temp where id=t.id and secondaryid<t.secondaryid),0) from #temp t order by id, secondaryid
November 3, 2004 at 11:40 am
Thank you. Your solution worked.
I keep forgetting abount using inequality operators while joining tables.
George Mastros
Orbit Software, Inc.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply