CODEWITHSUNDEEP
teradata
john
john

Reputation: 707

Teradata. How to sum all values within a group?

My initial table looks like this 

id  value
1   20
1   50
1   30
2   60 
2   5
2   35

I need the following resulting table 

id  value   cum         | ( this is explanation not a field) 
_ _ _ _ _ _ _ _ __ _ _ _ _ _ _ _ __ _ _ _ _ _ _ _ __ _ _ _ _ _ _ _ _
1   20      20          | (0  + 20 = 20) 
1   30      50          | (30 + 20 = 50) 
1   50      100         | (50 + 50 = 100) 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ __ _ _ _ _ _ _ _ __ _ _ _ _ _ _ _ _
2   5       5           | (0  + 5 = 5) 
2   35      40          | (5  + 35 = 40) 
2   60      100         | (40 + 60 = 100)

The logic is

1) ORDER the original table BY value ASC

2) SUM up all the previous values resulting in a cumulative cum field . So the cum column is the SUM of all value less than the current value .

I need to do this with sql only without a stored procedure

How can I do this?

Upvotes: 0

Views: 2267

Answers (1)

dnoeth
dnoeth

Reputation: 60482

Well, you describe a Cumulative Sum:

sum(value) 
over (partition by id 
      order by values
      rows unbounded preceding)

The rows unbounded preceding is needed in Teradata, because it defaults to rows unbounded preceding and unbounded following (a Group Sum), which is different than Standard SQL's default of range unbounded preceding.

Upvotes: 1

Related Questions