CODEWITHSUNDEEP
sqloracle-database
pizzafeet
pizzafeet

Reputation: 1

Calculate rolling monthly sum in SQL

I have data indicating the date in which a new customer was added. Meanwhile, there are also instances that that customer might've been lost. E.g.:

CUST_NO DATE_ADDED DATE_LOST
1 01-MAY-20 null
2 01-MAY-20 01-AUG-20
3 01-JUL-20 null
4 01-AUG-20 null
5 01-MAY-20 null
6 01-JUN-20 null
7 01-JUN-20 null
8 01-MAY-20 null

Rather than get a discrete count of customers by month, I'd like to view the data as a rolling total.

Desired output:

Month Count
May 4
Jun 6
Jul 7
Aug 7

What's the best way to get the above result? I'm in Oracle SQL Developer.

Upvotes: 0

Views: 430

Answers (2)

Caius Jard
Caius Jard

Reputation: 74710

It would be something like

SELECT
  DATE_ADDED,
  SUM(COUNT(*)) OVER(ORDER BY DATE_ADDED)
FROM T
GROUP BY DATE_ADDED

If your dates aren't always the first of the month, use TRUNC(DATE_ADDED, 'MM') instead

When you mix window functions with grouping, the group is done first.. this means the query groups and counts then performs the SUM window function on the grouped counts.. when a SUM OVER is used the default behavior is to perform a rolling sum of the data in order specified. The window spec is an abbreviation of OVER(ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - his means "on every row, add up all the previous rows back to the start of the partition" which effectively achieves a rolling sum

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271091

You can use aggregation with window functions:

select trunc(date_added, 'MON'), count(*) as cnt_in_month,
       sum(count(*)) over (order by trunc(date_added, 'MON')) as running_cnt
from t
group by trunc(date_added, 'MON');

Note: This truncates the date to the month, so the year is included. As a general rule, you want to include the year when working with months.

Upvotes: 0

Related Questions