Get running total per month and year from PostgreSQL database

Question

Here's my sample data (table "sumtest"):

+-------+--------+-------+
| month |  value |  year |
+-------+--------+-------+
|     1 |     10 |  2017 |
|     2 |      0 |  2017 |
|     2 |     10 |  2016 |  # note: different year
|     2 |      5 |  2017 |
|     3 |     88 |  2017 |
|     3 |      2 |  2017 |
|     5 |      1 |  2017 |
|     5 |      4 |  2017 |
|     5 |      5 |  2017 |
+-------+--------+-------+

I'd like to get the total value for each month, and the running total for each year in that particular month, i.e. I'd like my result to be this:

+------+-------+-----------+----------+
| year | month | sum_month | sum_year |
+------+-------+-----------+----------+
| 2016 |     2 |        10 |       10 |
| 2017 |     1 |        10 |       10 |
| 2017 |     2 |         5 |       15 |
| 2017 |     3 |        90 |      105 |
| 2017 |     5 |        10 |      115 |
+------+-------+-----------+----------+

I'm new to Postgres and I've tried the following:

    SELECT *, sum(value) OVER (PARTITION BY month, year) AS sum_month, 
sum(value) OVER (PARTITION BY year) AS sum_year
    FROM sumtest
    ORDER BY year, month

But this yields one line for each original entry, and the total yearly sum listed on each line instead of the cumulative sum up to this point:

+-------+-------+------+-----------+----------+
| month | value | year | sum_month | sum_year |
+-------+-------+------+-----------+----------+
|     2 |    10 | 2016 | '10'      | '10'     |
|     1 |    10 | 2017 | '10'      | '115'    |
|     2 |     5 | 2017 | '5'       | '115'    |
|     2 |     0 | 2017 | '5'       | '115'    |
|     3 |     2 | 2017 | '90'      | '115'    |
|     3 |    88 | 2017 | '90'      | '115'    |
|     5 |     4 | 2017 | '10'      | '115'    |
|     5 |     1 | 2017 | '10'      | '115'    |
|     5 |     5 | 2017 | '10'      | '115'    |
+-------+-------+------+-----------+----------+

I've also tried using GROUP BY, which worked for the cumulative sums of the months, but then I did not now how to include the running total of the year (as that is not supposed to be grouped by month).

Any help would be greatly appreciated.

Possible Duplicate : stackoverflow.com/questions/22841206/… — Andrew
– Andrew, Commented Jan 8, 2018 at 11:46
@Andrew That question is where I got the PARTITION BY-statement from, but I also need the grouping. — canavanin
– canavanin, Commented Jan 8, 2018 at 11:53

Kaushik Nayak · Accepted Answer · 2018-01-08 11:50:14Z

4

You could add MAX and GROUP BY above your query, along with ORDER BY inside OVER()

select year,month,MAX( sum_month) sum_month,  MAX(sum_year) sum_year 
FROM
(
 SELECT *, sum(value) OVER (PARTITION BY month, year ORDER BY year,month) AS sum_month, 
sum(value) OVER (PARTITION BY year ORDER BY year,month) AS sum_year
    FROM sumtest
)  a
GROUP BY year,month;

DEMO

answered Jan 8, 2018 at 11:50

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

canavanin Over a year ago

Works very nicely, thanks! One more question though: why is ORDER BY required inside OVER()? Thanks.

Kaushik Nayak Over a year ago

@canavanin: when you said running sum, you need a direction to run, don't you? Hence Order by specifies the order to evaluate the aggregation (SUM). If you don't specify the window clauses ROWS between, the default value with order by is running sum

Vao Tsun · Accepted Answer · 2018-01-08 11:47:22Z

1

prepare:

t=# create table s(a text,m int, v int, y int, b text);
CREATE TABLE
t=# copy s from stdin delimiter '|';
>> \.
COPY 9
t=# alter table s drop column a;
ALTER TABLE
t=# alter table s drop column b;
ALTER TABLE

query:

t=# select distinct y,m,sum(v) over (partition by m,y), sum(v) over (partition by y order by m) from s order by y,m;
  y   | m | sum | sum
------+---+-----+-----
 2016 | 2 |  10 |  10
 2017 | 1 |  10 |  10
 2017 | 2 |   5 |  15
 2017 | 3 |  90 | 105
 2017 | 5 |  10 | 115
(5 rows)

updated - I totally missed cumulative

answered Jan 8, 2018 at 11:47

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

Get running total per month and year from PostgreSQL database

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related