Over partition with order by in teradata, how to used

Database
Highlighted

Over partition with order by in teradata, how to used

dear all,

i have a sample of data like this just as illustration

data_date area total_revenue
1 indonesia 100
1 usa 500
2 indonesia 200
2 usa 500

when i query using over partition in Teradata which is

select data_date, area, sum(total_revenue), sum(sum(total_revenue)) over(partition by area order by data_date)
from sample
group by data_date, area
order by 2,1

i got

1 indonesia 100 300
2 indonesia 200 300
1 usa 500 1000
2 usa 500 1000

as i usually used using oracle, the above query should return something like this

1 indonesia 100 100
2 indonesia 200 300
1 usa 500 500
2 usa 500 1000

(1) how can i achieve above result on Teradata, or what was wrong in my query
(2) is Teradata over partition by already support with order by clause
1 REPLY
Supporter

Re: Over partition with order by in teradata, how to used

If there's ORDER BY in Oracle (and Standard SQL) the default is a Cumulative Sum, but in Teradata it defaults to a Group Sum.

You just have to add ROWS:
sum(sum(total_revenue)) over(partition by area order by data_date ROWS UNBOUNDED PRECEDING)

Dieter