General Welcome to the Teradata Forums. Read the guidelines on posting. Email notifications and RSS feeds are available, and also a chronological list of all forum activity.
 Print All Forums General nsaikrishna 5 posts Joined 01/12 22 Apr 2014 Help with Analytic Function I have a situation where I need to do the following: I have the below information ```AYear ASeason AMonth AWeek AUnit ASupplier ASalesArea AAmount 2013 1 1 1 25 35 1 10 2013 1 1 2 25 35 1 21 2013 1 1 3 25 35 1 23 2013 1 1 4 25 35 1 12 2013 1 2 1 25 35 1 143 2013 1 2 2 25 35 1 42 2013 1 2 3 25 35 1 12 2013 1 1 1 25 35 2 98 2013 1 1 2 25 35 2 27 2013 1 1 3 25 35 2 38 2013 1 1 4 25 35 2 23 2013 1 2 1 25 35 2 12 2013 1 2 2 25 35 2 45 2013 1 2 3 25 35 2 32 ``` I need to calculate 1. Season to date Cumulative Sum of Amount by Unit, Supplier and Sales Area.  I am able to get to this using the following SUM(AAMOUNT)  OVER (PARTITION BY AYEAR,ASEASON,AUNIT,ASUPPLIER,ASALESAREA ORDER BY AMONTH, AWEEK ROWS BETWEEN UNBOUNDED PRECEDING  AND CURRENT ROW)   2. Season to date Cumulative Sum of Amount by Unit and Supplier I am unable to get to this. If I use the approach above, it randomly sums 1 ASalesArea after the other. But my expectation is, if the partition by + order by yields more than 1 row all of the rows need to have the same amount.    Below is my expected result:   ```Year Season Month Week Dept Vend Loc Amount Season to Date Season to Date Sum by Loc Sum by Vend 2013 1 1 1 25 35 1 10 10 108 2013 1 1 2 25 35 1 21 31 156 2013 1 1 3 25 35 1 23 54 217 2013 1 1 4 25 35 1 12 66 252 2013 1 2 1 25 35 1 143 209 407 2013 1 2 2 25 35 1 42 251 494 2013 1 2 3 25 35 1 12 263 538 2013 1 1 1 25 35 2 98 98 108 2013 1 1 2 25 35 2 27 125 156 2013 1 1 3 25 35 2 38 163 217 2013 1 1 4 25 35 2 23 186 252 2013 1 2 1 25 35 2 12 198 407 2013 1 2 2 25 35 2 45 243 494 2013 1 2 3 25 35 2 32 275 538 ```   The last column is what I expect. Any suggestions that you can provide is really appreciated. Thank you, Sai Tags: partition by dnoeth 4628 posts Joined 11/04 22 Apr 2014 Hi Sai, you can't get this in a single pass, you need to use a Derived Table: ```SELECT dt.*, SUM(A) OVER (PARTITION BY AYEAR,ASEASON,AUNIT,ASUPPLIER, AMONTH, AWEEK) AS SumByVend FROM ( SELECT ... SUM(AAMOUNT) OVER (PARTITION BY AYEAR,ASEASON,AUNIT,ASUPPLIER,ASALESAREA ORDER BY AMONTH, AWEEK ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SumByLoc FROM tab ) AS dt ```  Dieter nsaikrishna 5 posts Joined 01/12 23 Apr 2014 Thank you Dieter. I really appreciate your quick response. You must sign in to leave a comment. Active Posters