16.20 - Example: Cumulative Sum - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Functions, Expressions, and Predicates

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

The following query returns the cumulative balance per account ordered by transaction date:

   SELECT acct_number, trans_date, trans_amount,
   SUM(trans_amount) OVER (PARTITION BY acct_number 
                           ORDER BY trans_date 
                           ROWS UNBOUNDED PRECEDING) as balance
   FROM ledger
   ORDER BY acct_number, trans_date;

Here are the possible results of the preceding SELECT.

acct_number trans_date trans_amount balance
73829 1998-11-01 113.45 113.45
73829 1988-11-05 -52.01 61.44
73929 1998-11-13 36.25 97.69
82930 1998-11-01 10.56 10.56
82930 1998-11-21 32.55 43.11
82930 1998-11-29 -5.02 38.09