Re: Showing a cumlative total by month

On Fri, Sep 11, 2015 at 10:15 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Fri, Sep 11, 2015 at 10:46 AM, David Nelson <dnelson77808(at)gmail(dot)com>
wrote:
>>
>> I would like to add a running total column to the view, but can't quite
get there. I figured out how to show the information I want using a CTE,
but I can not figure out how to translate that to a single query to define
a view with. So I'm hoping someone can point me in the right direction.
>>
>
> --> http://www.postgresql.org/docs/9.3/interactive/functions-window.html
>
> David J.
>

Thanks David and Stuart!

these two solutions seem to be what I'm after. I will apply them to the
production view (which is more complex), and see how they play out. But you
two pointed me in the directions that I needed to go.

DROP VIEW upload_info;

CREATE VIEW upload_info AS
SELECT mon,
monthly_total,
SUM(monthly_total) OVER (ORDER BY mon) AS cumulative
FROM (SELECT CONCAT(EXTRACT(YEAR FROM upload_date),
'-',
LPAD(CAST(EXTRACT(MONTH FROM upload_date) AS TEXT), 2,
'0')) AS mon,
SUM(upload_size) AS monthly_total
FROM uploaded_files u
GROUP BY EXTRACT(MONTH FROM upload_date), EXTRACT(YEAR FROM
upload_date)
ORDER BY EXTRACT(YEAR FROM upload_date), EXTRACT(MONTH FROM
upload_date)) AS u;

SELECT *
FROM upload_info;

mon | monthly_total | cumulative
---------+----------------+----------------
2014-01 | 3179135699 | 3179135699
2014-02 | 1634499060 | 4813634759
2014-03 | 4278982588 | 9092617347
2014-04 | 142238544857 | 151331162204
2014-05 | 357240707209 | 508571869413
2014-06 | 64339859968 | 572911729381
2014-07 | 25513017728 | 598424747109
2014-08 | 11020669492 | 609445416601
2014-09 | 1018774598 | 610464191199
2014-10 | 76752535951 | 687216727150
2014-11 | 4611404964 | 691828132114
2014-12 | 69607199452 | 761435331566
2015-01 | 15700525 | 761451032091
2015-02 | 34234715981 | 795685748072
2015-03 | 1484150449194 | 2279836197266
2015-04 | 13096208914706 | 15376045111972
2015-05 | 814385166 | 15376859497138
2015-06 | 563829413 | 15377423326551
2015-07 | 656007272 | 15378079333823
2015-08 | 1828956674258 | 17207036008081
2015-09 | 822601322 | 17207858609403
(21 rows)

DROP VIEW upload_info;

CREATE VIEW upload_info AS
WITH upload_summary AS
(
SELECT EXTRACT(YEAR FROM upload_date) AS up_year,
LPAD(CAST(EXTRACT(MONTH FROM upload_date) AS TEXT), 2, '0') AS
up_month,
SUM(upload_size) AS monthly_total
FROM uploaded_files
GROUP BY up_month, up_year
)
SELECT u.up_year,
u.up_month,
u.monthly_total,
(SELECT SUM(monthly_total)
FROM upload_summary
WHERE CAST(CONCAT(up_year,
'-',
up_month,
'-01 00:00:00-05')
AS TIMESTAMP) <=
CAST(CONCAT(u.up_year,
'-',
u.up_month,
'-01 00:00:00-05')
AS TIMESTAMP)
) AS cumulative
FROM upload_summary u
ORDER BY up_year, up_month;

SELECT *
FROM upload_info;

up_year | up_month | monthly_total | cumulative
---------+----------+----------------+----------------
2014 | 01 | 3179135699 | 3179135699
2014 | 02 | 1634499060 | 4813634759
2014 | 03 | 4278982588 | 9092617347
2014 | 04 | 142238544857 | 151331162204
2014 | 05 | 357240707209 | 508571869413
2014 | 06 | 64339859968 | 572911729381
2014 | 07 | 25513017728 | 598424747109
2014 | 08 | 11020669492 | 609445416601
2014 | 09 | 1018774598 | 610464191199
2014 | 10 | 76752535951 | 687216727150
2014 | 11 | 4611404964 | 691828132114
2014 | 12 | 69607199452 | 761435331566
2015 | 01 | 15700525 | 761451032091
2015 | 02 | 34234715981 | 795685748072
2015 | 03 | 1484150449194 | 2279836197266
2015 | 04 | 13096208914706 | 15376045111972
2015 | 05 | 814385166 | 15376859497138
2015 | 06 | 563829413 | 15377423326551
2015 | 07 | 656007272 | 15378079333823
2015 | 08 | 1828956674258 | 17207036008081
2015 | 09 | 822601322 | 17207858609403
(21 rows)