All Forums Database
ahsaniah 1 post Joined 11/08
03 May 2009
Over partition with order by in teradata, how to used

dear all,i have a sample of data like this just as illustrationdata_date area total_revenue1 indonesia 1001 usa 5002 indonesia 2002 usa 500when i query using over partition in Teradata which isselect data_date, area, sum(total_revenue), sum(sum(total_revenue)) over(partition by area order by data_date) from samplegroup by data_date, areaorder by 2,1 i got 1 indonesia 100 3002 indonesia 200 3001 usa 500 10002 usa 500 1000as i usually used using oracle, the above query should return something like this 1 indonesia 100 1002 indonesia 200 3001 usa 500 5002 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

dnoeth 3247 posts Joined 11/04
04 May 2009

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

Dieter

You must sign in to leave a comment.