I have a table in Teradata as below
| Record_id | sub_id | amount | seq_no | total_unwind |
|---|---|---|---|---|
| 123 | 0830 | 1.00 | 1 | ? |
| 123 | 0830 | 3.00 | 2 | ? |
| 123 | 0830 | 4.00 | 3 | ? |
| 456 | 0850 | 2.00 | 1 | ? |
| 456 | 0850 | 1.00 | 2 | ? |
| 456 | 0850 | 5.00 | 3 | ? |
I want the sum of amount in total_unwind column by grouping record_id and sub_id, and ordering seq_no.
For eg. for record_id 123 and sub_id 830,
For seq_no 1, total_unwind will be same as amount. Total_unwind=1.00
For seq_no=2, the total_unwind will be 1.00(previous total_unwind)+ 3.00(current amount). Total_unwind=4.00
For seq_no=3, the total_unwind will be 4.00 (previous total_unwind)+ 4.00(current amount). Total_unwind=8.00
The sum should start again for the new set of record_id and sub_id
The output should look like below:
| Record_id | sub_id | amount | seq_no | total_unwind |
|---|---|---|---|---|
| 123 | 0830 | 1.00 | 1 | 1.00 |
| 123 | 0830 | 3.00 | 2 | 4.00 |
| 123 | 0830 | 4.00 | 3 | 8.00 |
| 456 | 0850 | 2.00 | 1 | 2.00 |
| 456 | 0850 | 1.00 | 2 | 3.00 |
| 456 | 0850 | 5.00 | 3 | 8.00 |
I tried the below query in Teradata
sel record_id,sub_id,amount,seq_no, sum(amount) over (order by sub_id,SEQ_NO ROWS UNBOUNDED PRECEDING ) as total_unwind from table_name
The output for the above query is as below
| Record_id | sub_id | amount | seq_no | total_unwind |
|---|---|---|---|---|
| 123 | 0830 | 1.00 | 1 | 1.00 |
| 123 | 0830 | 3.00 | 2 | 4.00 |
| 123 | 0830 | 4.00 | 3 | 8.00 |
| 456 | 0850 | 2.00 | 1 | 10.00 |
| 456 | 0850 | 1.00 | 2 | 11.00 |
| 456 | 0850 | 5.00 | 3 | 16.00 |
It sums the value continuously till the end which is not needed. Can someone please help with using group by on record_id,sub_id
