Can someone guide me in calculating the sum in the case

Database
Enthusiast

Can someone guide me in calculating the sum in the case

Table data




Sys Num Id  Descr
0001 10 Provider
0001 10 Pro
0002 84 Service
0003 24 Buisness
0003 24 Admin
0003 24 Finance
0004 41 Philosophy

All i want is to calculate the running sum based on the SysNum and Id Field.The desired o/p is




Sys Num Id  Descr Sum
0001 10 Provider -1
0001 10 Pro -1
0002 84 Service -2
0003 24 Buisness -3
0003 24 Admin -3
0003 24 Finance -3
0004 41 Philosophy -4

I tried using sum (-1) over( order by sys_num,id rows unbound preceding) but the result i get is in sequence (-1,-2-3).

Tried using Partition too but the sum is reset when the sys_num and id combination changes.

Thanks,

Kathir.

3 REPLIES
Teradata Employee

Re: Can someone guide me in calculating the sum in the case

Looks like you want DENSE_RANK, which is not implemented natively in Teradata.

See Dieter's blog http://developer.teradata.com/blog/dnoeth/2011/05/missing-functions-dense-rank

Enthusiast

Re: Can someone guide me in calculating the sum in the case

this one might give a better picture than the previous one




Sys Num, ID, Descr
0001, 10, Provider
0001, 10, Pro
0002, 84, Service
0003, 24, Buisness
0003, 24, Admin
0003, 24, Finance
0004, 41, Philosophy

Expected o/p is  




Sys Num,   ID,      Descr,           Sum       
0001,           10,      Provider          -1
0001,           10,      Pro                 -1
0002,           84,      Service            -2
0003,           24,      Buisness         -3
0003,           24,      Admin             -3
0003,           24,      Finance           -3
0004,           41,      Philosophy      -4
Enthusiast

Re: Can someone guide me in calculating the sum in the case

thanks for the info Fred...

Got the desired o/p.