Sum over period

Question

I have some doubts regarding a sum of rows. I have the following dataset in Teradata SQL Assistant:

id period avg_amt flag
111 1   123.5      1
211 1   143.1      1
311 2   122.1      1
411 3   214.5      1
511 3   124.6      0
611 3   153.2      1

I would like to sum the flags based on the period. What I tried is to use the sum function over the period in two different ways:

select 
       id, period, avg_amt, flag, sum(flag) over (partition by id order by period)
from dataset

and

select 
       id, period, avg_amt, flag, sum(flag)
       group by id, period, avg_amt, flag
from dataset

The output does not return what I should expect, i.e. for period 1 sum=3, period 2 sum 1, period 3 sum 2.

Could you please tell me what is wrong? Thanks

Answer 1

To get the simple sum:

select period, sum(flag) total_flag
from dataset
group by period

In SQL server, to add back in the rest of the information, you can use a subquery and join it back in:

select id, dataset.period, avg_amt, flag, total_flag
from dataset
inner join (
   select period, sum(flag) total_flag
   from dataset
   group by period
) TF on TF.period=dataset.period

I hope this is still good with teradata-sql-assistant.