-1

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!

AvocadoRivalry
  • 411
  • 1
  • 7
  • 16

1 Answers1

0

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

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