Showing a cumlative total by month

From: David Nelson <dnelson77808(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Showing a cumlative total by month
Date: 2015-09-11 14:46:25
Message-ID: CANxyCUGMBeJqKo77AGNEvYsSSHZtYGE8RsVQBVJHMx4H2p-Sbg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Good morning all,

We have a system that maintains information about files that have been
uploaded to our system. I have a view that shows for each month the total
size of all files submitted tht month. 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.

SELECT VERSION();
version

----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.18 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.4.7-11), 64-bit

DROP TABLE uploaded_files;

CREATE TABLE uploaded_files
(
file_id SERIAL NOT NULL,
upload_date TIMESTAMP WITH TIME ZONE NOT NULL,
upload_size BIGINT NOT NULL,
PRIMARY KEY (file_id)
);

This test case is populated with a handful of random records (659), and the
following CTE seems to extract the information I am after (still not
positive I have the date down exactly, but I can deal with that):

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;

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

The basic view definition gives the first three columns (date fields
concatenated into one):
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);

mon | monthly_total
---------+----------------
2014-01 | 3179135699
2014-02 | 1634499060
2014-03 | 4278982588
2014-04 | 142238544857
2014-05 | 357240707209
2014-06 | 64339859968
2014-07 | 25513017728
2014-08 | 11020669492
2014-09 | 1018774598
2014-10 | 76752535951
2014-11 | 4611404964
2014-12 | 69607199452
2015-01 | 15700525
2015-02 | 34234715981
2015-03 | 1484150449194
2015-04 | 13096208914706
2015-05 | 814385166
2015-06 | 563829413
2015-07 | 656007272
2015-08 | 1828956674258
2015-09 | 822601322

I just can't figure out how to specify the summation of the file sizes
through each month in a static query. Any suggestions?

Thanks,
David

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message David G. Johnston 2015-09-11 15:15:35 Re: Showing a cumlative total by month
Previous Message Rene Romero Benavides 2015-08-25 20:46:31 Re: Reassign permissions