Calculating Running Totals, keeping constant for each month

Question

I need to calculate the running total, would like a constant number for each month, only to have it increase by a specific amount for each succeeding month. However I can't group or partition out the dates to do this... and I only know about the code to write a continuous running total.

I've tried this:

SELECT 
    monthdates,
    sum(10) OVER (
        PARTITION BY monthdates ORDER BY monthdates ASC rows between unbounded preceding and current row)
FROM mytable;

..which is wrong because I want this:

+------------+-----+
| monthdates | sum |
+------------+-----+
| 2018-01-01 |  10 |
| 2018-01-01 |  10 |
| 2018-02-01 |  20 |
| 2018-02-01 |  20 |
| 2018-02-01 |  20 |
| 2018-02-01 |  20 |
| 2018-02-01 |  20 |
| 2018-03-01 |  30 |
| 2018-03-01 |  30 |
+------------+-----+

How do I approach this problem? Thanks in advance!

Answer 1

First get the running sum over the distinct monthdates and then join them to your table on the monthdates.

SELECT t2.monthdates,
       x2.sum
       FROM mytable t2
            INNER JOIN (SELECT x1.monthdates,
                               sum(10) OVER (ORDER BY x1.monthdates) sum
                               FROM (SELECT DISTINCT
                                            t1.monthdates
                                            FROM mytable t1) x1) x2
                       ON x2.monthdates = t2.monthdates
       ORDER BY t2.monthdates;

You could solve it even easier by using dense_rank() multiplied by 10, but without sum().

SELECT t1.monthdates,
       dense_rank() OVER (ORDER BY t1.monthdates) * 10 sum
       FROM mytable t1
       ORDER BY t1.monthdates;

db<>fiddle