I need to do cumulative sum on one of the field(value column from above data) along with grouping it by month. Here is how my data looks:

I need the output as follows:

I am doing this in Impala, I tried couple of things, but it did not worked.

Thank you for your help!

CodePudding user response：

Since analytic functions are applied to the select list expressions after the aggregation, you may wrap aggregation sum with an analytic sum ordered by month:

with t(id, dt, val) as (
  select *
  from(values
    (1, date '2022-01-10', 9),
    (2, date '2022-01-15', 8),
    (3, date '2022-02-14', 9),
    (4, date '2022-02-18', 12),
    (5, date '2022-02-23', 15)
  ) a
)
select
  date_trunc('month', dt) as mon
  , sum(sum(val)) over(
    order by date_trunc('month', dt) asc
  ) as rsum
from t
group by 1

mon                    | rsum
:--------------------- | ---:
2022-01-01 00:00:00 00 |   17
2022-02-01 00:00:00 00 |   53

db<>fiddle

SELECT MONTH, SUM(value) FROM yourtable GROUP BY MONTH(month)

  select
      date_trunc(date,'month') as month,
      sum(value) as total
    from [table name]
    group by 1