Cumulative Totals By Date in Postgres Using Window Function

Question

I'm looking to produce a simple cumulative total by date in SQL, but running into a seemingly-simple issue. I have developed a work around, but imagine there's a way to do what I want without the extra step. Basically what I'm looking for is:

| Date | Count | | 2015-01-01 | 1 | | 2015-01-02 | 5 | | 2015-01-03 | 8 | | 2015-01-04 | 9 | | 2015-01-05 | 9 |

We can assume that the table has only two rows - id and date - and what I would like to see is exactly what is above; however, when I run the following code, I get repeated rows:

SELECT date, count(*) over (order by date) AS cumulative_count FROM my_table;

Returns:

| Date | Count | | 2015-01-01 | 1 | | 2015-01-02 | 5 | | 2015-01-02 | 5 | | 2015-01-02 | 5 | | 2015-01-02 | 5 | | 2015-01-02 | 5 | | 2015-01-03 | 8 | | 2015-01-03 | 8 | ...etc.

My workaround solution has been to use SELECT DISTINCT date, count(*) over (order by date) AS cumulative_count FROM my_table; but I'd imagine there's a better way that I'm just missing.

Thanks in advance!

I don't think there's anything wrong with being `DISTINCT` here. See also: http://stackoverflow.com/questions/5698452/count-cumulative-total-in-postgresql — G. Cito, Mar 02 '15 at 18:54
Is there a more simple way to get cumulative/running totals without using a window function? — AvocadoRivalry, Mar 02 '15 at 19:20

score 0 · Answer 1 · answered Mar 02 '15 at 19:10

0

if you don't want to use distinct you could use a GROUP BY DATE, COUNT

answered Mar 02 '15 at 19:10

DCR

14,737
12
52
115

`group by` would not compute a cumulative total but a total `by date` unless you do it in a subquery and then do the cummulative total in the outer query. But that is more trouble than just use `distinct` – Clodoaldo Neto Mar 02 '15 at 19:17

Cumulative Totals By Date in Postgres Using Window Function

1 Answers1