Advanced Summation with SQL

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Fan

Advanced Summation with SQL

Hi there,

 

I have data looking like below. I need to calculate the total points of player in every 3 consequitive games. For example, player 1 played only 3 games , so I need some of 10+3+2. But player 2 played 5 games, so for that player, I need 3 records. One with sum of points of first 3  games (8+3+7) and another with the second set (3+7+11) and one with the last set of 3 games (7+11+13). How do I achieve this with SQL?

 

Playergameponts
1110
133
182
238
243
257
2611
2713

 

Thanks

Nair

1 REPLY
Junior Contributor

Re: Advanced Summation with SQL

 

select player,
   sum(points)
   over (partition by player
         order by game
         rows 2 preceding)    -- includes current row -> 3 rows
from tab
qualify
   count(*)
   over (partition by player 
         order by game
         rows 2 preceding) = 3   -- exactly three rows, i.e. first two rows per player are removed