CODEWITHSUNDEEP
postgresqlcumulative-sum
stefan
stefan

Reputation: 115

Calculate running total per month

Let's say I have this table:

id; date; units
1; Jan 1; 1
2; Jan 2; 4
3; Feb 9; 6
4; Mar 1; 1
5; Mar 4; 2

How can I now calculate the "accumulated_month" column accordingly? The calculation should start anew with every new month.

id; date; units; accumulated_month
1; Jan 1; 1; 1
2; Jan 2; 4; 5
3; Feb 9; 6; 6
4; Mar 1; 1; 1
5; Mar 4; 2; 3

All I manage to get is this:

id; date; units; accumulated_month
1; Jan 1; 1; 5
2; Jan 2; 4; 5
3; Feb 9; 6; 6
4; Mar 1; 1; 3
5; Mar 4; 2; 3

Upvotes: 2

Views: 1975

Answers (2)

caiohamamura
caiohamamura

Reputation: 2728

Run this:

SELECT
    t1.id, 
    t1.date, 
    t1.units,
    SUM(t2.units) accumulated_month
FROM t t1 
    JOIN t t2 
        ON date_trunc('month', t1.date) = date_trunc('month', t2.date)
            AND t2.date <= t1.date
GROUP BY t1.id, t1.date, t1.units
ORDER BY t1.id

EDIT:

Simplified SQL:

SELECT
    t1.*, SUM(t2.units) accumulated_month
FROM t t1 
    JOIN t t2 
        ON date_trunc('month', t1.date) = date_trunc('month', t2.date)
            AND t2.date <= t1.date
GROUP BY t1.id
ORDER BY t1.id

Logic

The logic behind is to JOIN the table with itself. Then for each row of t1 JOIN all rows in t2 that satisfies both (AND):

  1. same year/month
  2. t2.date <= t1.date

With those ON constraints each row in t1 will be JOIN with accumulated in that month so far. So without grouping, you'd get something like:

╔════╦════════════╦═══════╦════════════╦══════════╗
║ id ║    date    ║ units ║  t2_date   ║ t2_units ║
╠════╬════════════╬═══════╬════════════╬══════════╣
║  1 ║ 2016-01-01 ║     1 ║ 2016-01-01 ║        1 ║
║  2 ║ 2016-01-02 ║     4 ║ 2016-01-01 ║        1 ║
║  2 ║ 2016-01-02 ║     4 ║ 2016-01-02 ║        4 ║
║  3 ║ 2016-02-09 ║     6 ║ 2016-02-09 ║        6 ║
║  4 ║ 2016-03-01 ║     1 ║ 2016-03-01 ║        1 ║
║  5 ║ 2016-03-04 ║     2 ║ 2016-03-01 ║        1 ║
║  5 ║ 2016-03-04 ║     2 ║ 2016-03-04 ║        2 ║
╚════╩════════════╩═══════╩════════════╩══════════╝

After GROUP BY t1.id you can SUM(t2.units) to get what you expect.

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

create table t (id int, date date, units int);
insert into t (id, date, units) values
(1, '2016-01-01', 1),
(2, '2016-01-02', 4),
(3, '2016-02-09', 6),
(4, '2016-03-01', 1),
(5, '2016-03-04', 2);

Not clear if you want the month total or a running total within the month. For the month total:

select
    id, date, units,
    sum(units) over (partition by date_trunc('month', date)) as acumm
from t
order by 1,2
;
 id |    date    | units | acumm 
----+------------+-------+-------
  1 | 2016-01-01 |     1 |     5
  2 | 2016-01-02 |     4 |     5
  3 | 2016-02-09 |     6 |     6
  4 | 2016-03-01 |     1 |     3
  5 | 2016-03-04 |     2 |     3

If you want a running total within the month then add an order by to the window function:

select
    id, date, units,
    sum(units) over (
        partition by date_trunc('month', date)
        order by id
    ) as acumm
from t
order by 1,2
;
 id |    date    | units | acumm 
----+------------+-------+-------
  1 | 2016-01-01 |     1 |     1
  2 | 2016-01-02 |     4 |     5
  3 | 2016-02-09 |     6 |     6
  4 | 2016-03-01 |     1 |     1
  5 | 2016-03-04 |     2 |     3

Upvotes: 2

Related Questions