General
Enthusiast

## 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 Vend2013      1     1    1   25   35   1     10             10              1082013      1     1    2   25   35   1     21             31              1562013      1     1    3   25   35   1     23             54              2172013      1     1    4   25   35   1     12             66              2522013      1     2    1   25   35   1    143            209              4072013      1     2    2   25   35   1     42            251              4942013      1     2    3   25   35   1     12            263              5382013      1     1    1   25   35   2     98             98              1082013      1     1    2   25   35   2     27            125              1562013      1     1    3   25   35   2     38            163              2172013      1     1    4   25   35   2     23            186              2522013      1     2    1   25   35   2     12            198              4072013      1     2    2   25   35   2     45            243              4942013      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 (1)
2 REPLIES
Senior Apprentice

## Re: Help with Analytic Function

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`
Enthusiast

## Re: Help with Analytic Function

Thank you Dieter. I really appreciate your quick response.