Database
Fan

## Re: Cumulative Sum by distinct customer ID by year

Sorry for the confusion. I need to get distinct count of col5. Need to populate the aggregrate in decremental order.

`1. 123 7/2/2014    VZ  2 -- here two is count which is distinct count(VZ,SP,VZ,SP) `

`2. 123 6/29/2014   SP  2 `-- here two is count which is distinct count(SP,VZ,SP)

`3. 123 6/24/2014   VZ  2`-- here two is count which is distinct count(VZ,SP)

`4. 123 6/23/2014   SP  1`-- here one is the count which is distinct count(SP only) and there are no rows below.

I need to calculate the count col. like mentioned above

Junior Contributor

## Re: Cumulative Sum by distinct customer ID by year

You need to nest OLAP functions, tag the first row with a new col5 value and then do a cumulative sum:

`SELECT customer, col4, col5,    SUM(x)    OVER (PARTITION BY customer         ORDER BY col4         ROWS UNBOUNDED PRECEDING)FROM (   SELECT customer, col4, col5,      CASE WHEN ROW_NUMBER() OVER (PARTITION BY customer, col5 ORDER BY col4) = 1 THEN 1 ELSE 0 END AS x   FROM tab   GROUP BY 1,2,3 ) AS dt`
Fan

Thanks Dieter.

Enthusiast

## Re: Cumulative Sum by distinct customer ID by year

Hello Folks,

I have a requirement and should bring d data in below format

(col1) (Date1) (Date2) (Sum for days1-10.) (Sum days11-20) (sumdays21-30)    Total

A.        Xx.     Vvv.          532                                                                             532

B.      Cc          Cv.                                           453                                            453

c.       Ft.         As.                                                                     543.                543

From d above example the date1 and date 2 are date Columns and it's being compared and difference is found as number of days . i.e. if date1>date 2 then date1-date2 else date2-date1 which would giv d number of days difference.

Now based on this number of days difference calculation the SUM is calculated group by on column 1 and will come under the respective days bucket, either (Sum for days1-10.)or (Sum days11-20) or  (sumdays21-30).

And the last column is the SUM of that specific row.

Best Regards,

Shavyani :)

## Re: Cumulative Sum by distinct customer ID by year

Hi shavyani

I don't know what do you want to do exactly but I think this can help you

## Re: Cumulative Sum by distinct customer ID by year

Hi shavyani

I don't know what do you want to do exactly but I think this can help you

`create multiset volatile table MyDates(date1 date,date2 date)primary index( date1 )on commit preserve rows;insert into MyDatesselect current_date+random(-15,15), current_date+random(-15,15)from sys_calendar.calendarsample 100;select  date1,  date2,  case when diferencia between 1 and 10 then diferencia else 0 end as Days_1_10,  case when diferencia between 11 and 20 then diferencia else 0 end as Days_11_20,  case when diferencia between 21 and 30 then diferencia else 0 end as Days_21_30,  abs(date1-date2) as diferenciafrom MyDateswhere abs(date1-date2) between 1 and 30`
Enthusiast

## Re: Cumulative Sum by distinct customer ID by year

SEL

CUST, DT1, DT2,

CASE   WHEN DIFF >=1 AND DIFF <=10 THEN DIFF ELSE 0 END AS SUM_1TO10,

CASE   WHEN DIFF >=11 AND DIFF <=20 THEN DIFF ELSE 0 END AS SUM_11TO20,

CASE   WHEN DIFF >=21 AND DIFF <=30 THEN DIFF ELSE 0 END AS SUM_21TO30,

CASE   WHEN DIFF <1 OR  DIFF >30 THEN DIFF ELSE 0 END AS SUM_DEF,

SUM_1TO10+SUM_11TO20+SUM_21TO30+SUM_DEF AS SUM_TOT FROM

(SEL   CUST, DT1, DT2,

SUM(

CASE

WHEN DT1 > DT2 THEN DT1-DT2

WHEN DT2 > DT1 THEN DT2-DT1

ELSE 0

END) OVER (PARTITION BY CUST ORDER BY 1 ) AS DIFF

FROM

(

SEL * FROM   (SEL 'A' AS CUST, DATE '2001-01-01' AS DT1, DATE '2001-01-11' AS DT2) A

UNION ALL

SEL * FROM   (SEL 'B' AS CUST, DATE '2001-01-21' AS DT1, DATE '2001-01-01' AS DT2) B

UNION ALL

SEL * FROM   (SEL   'C' AS CUST, DATE '2001-01-01' AS DT1, DATE '2001-01-31' AS DT2) C

UNION ALL

SEL * FROM   (SEL 'A' AS CUST, DATE '2001-02-21' AS DT1, DATE '2001-01-11' AS DT2) A

) A

) A

Enthusiast

## Re: Cumulative Sum by distinct customer ID by year

Thanks ton Atar and  Yuvae I shall implement this and get back.

Yuvae a small clarfication I would like to have , here is the union all clauses is being done based on the Cust value but if der are 1000 Cust values we wouldn't b able to write as many Union All, So is there an alternate to this Union All loop.

Best Regards,

Shavyani

Junior Contributor

## Re: Cumulative Sum by distinct customer ID by year

The UNION is just for creating the base data, of course this will be a Select on your actual table.

Enthusiast

## Re: Cumulative Sum by distinct customer ID by year

Exactly thought the same. Thanks ton for clarifying.

Regards,
Shavyani