A different kind of running total in Teradata

Question

I have seen tickets about running totals, but this is a little different.

Let's say I have claims from January 2020 to max(date). I want to write a query to give me the claims totals for January 2020, then January to February 2020, then January to March 2020.... all the way to January to max(date), and all in the same query.

An additional month of data gets added each month. I would like the query to account for that and not hardcode anything.

Are you saying that each month you want an additional column in the result? Then you would need to dynamically construct the query in a Stored Procedure or client-side scripting language. — Fred, Jan 19 '21 at 15:58

score 0 · Answer 1 · answered Jan 19 '21 at 14:52

This is a cumulative sum. Something like this:

select date, sum(claim) over (order by date)
from t;

If you need to aggregate by month, then:

select extract(year from date), extract(month from date),
       sum(claims) as claims_in_month,
       sum(sum(claims)) over (order by min(date)) as running_claims
from t
group by extract(year from date), extract(month from date);

A different kind of running total in Teradata

1 Answers1