sql - Sql Oracle 系统日期

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

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

谢谢你

标签： sqloracle

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);