Sql Oracle sysdate

Question

我的问题需要帮助

select count(item_number),
       trunc(creation_date, 'MON') as creation_date
 from EGP_SYSTEM_ITEMS_B 
where organization_id='300000021164768'
group by trunc(creation_date, 'MON')

输出为

1-3-2021: 200
1-4-2021: 150
1-5-2021: 300

我想要如下输出：

1-4-2021: the sum of the previous count will be 350
1-5-2021: will be 650

我想要当天和上个月的结果以及每个月的总和，谢谢

谢谢你

Answer 1

您可以在汇总的同时运行合计：

select trunc(creation_date, 'MON') as creation_date,
       count(*) as this_month_cnt,
       sum(count(*)) over (partition by organization_id, order by trunc(creation_date, 'MON')) as running_cnt
from EGP_SYSTEM_ITEMS_B 
where organization_id = '300000021164768'
group by trunc(creation_date, 'MON')
order by min(creation_date);

Answer 2

听起来你只是想要一个运行的总数

with monthly_data as (
  select count(item_number) cnt,
         trunc(creation_date, 'MON') as creation_date
   from EGP_SYSTEM_ITEMS_B 
  where organization_id='300000021164768'
  group by trunc(creation_date, 'MON')
)
select creation_date,
       cnt,
       sum(cnt) over (order by creation_date) running_cnt
  from monthly_data