Get a cumulative sum for each month in oracle sql

Question

Hi I have a table with 3 columns.

custid  p_date          vegetable
0000009 07-APR-20       tomato
0000013 25-MAR-20       potato
0000015 11-APR-19       tomato
0000016 23-JUL-18       potato
0000019 19-JUL-18       potato
0000018 07-JUN-20       turnip
0000020 12-JUL-18       turnip
0000022 23-JUL-18       potato
0000023 12-JUL-18       turnip
0000024 11-AUG-20       potato

I need to calculate the distinct count for the customers based on the veggie column along with cumulative sum for each month

the output should have below columns

date        
tomato_ct 
cum_tomato_ct
potato_ct
cum_potato_ct
turnip_ct        
cum_turnip_ct

Answer 1

If you want monthly counts and cumulative counts pivoted by vegetable, you can do:

select trunc(p_date, 'month') as p_month,
    sum(case when vegetable = 'tomato' then 1 else 0 end) as tomato_ct,
    sum(sum(case when vegetable = 'tomato' then 1 else 0 end)) over(order by trunc(p_date, 'month')) as cum_tomato_ct,
    sum(case when vegetable = 'potato' then 1 else 0 end) as potato_ct,
    sum(sum(case when vegetable = 'potato' then 1 else 0 end)) over(order by trunc(p_date, 'month')) as cum_potato_ct,
    sum(case when vegetable = 'turnip' then 1 else 0 end) as turnip_ct,
    sum(sum(case when vegetable = 'turnip' then 1 else 0 end)) over(order by trunc(p_date, 'month')) as cum_turnip_ct
from mytable
group by trunc(p_date, 'month')

You mentioned that you want a count of distinct customers. If you want to count each customer only once par vegetable, on their earliest appearance, then I would recommend two levels of aggregation:

select trunc(p_date, 'month') as p_month,
    sum(case when vegetable = 'tomato' then 1 else 0 end) as tomato_ct,
    sum(sum(case when vegetable = 'tomato' then 1 else 0 end)) over(order by trunc(p_date, 'month')) as cum_tomato_ct,
    sum(case when vegetable = 'potato' then 1 else 0 end) as potato_ct,
    sum(sum(case when vegetable = 'potato' then 1 else 0 end)) over(order by trunc(p_date, 'month')) as cum_potato_ct,
    sum(case when vegetable = 'turnip' then 1 else 0 end) as turnip_ct,
    sum(sum(case when vegetable = 'turnip' then 1 else 0 end)) over(order by trunc(p_date, 'month')) as cum_turnip_ct
from (
    select cust_id, vegetable, min(p_date) as p_date
    from mytable
    group by cust_id, vegetable
) t
group by trunc(p_date, 'month')