Re: Showing a cumlative total by month - Mailing list pgsql-sql

<div dir="ltr">On Fri, Sep 11, 2015 at 10:15 AM, David G. Johnston <<a
href="mailto:david.g.johnston@gmail.com">david.g.johnston@gmail.com</a>>wrote:<br />><br />> On Fri, Sep 11,
2015at 10:46 AM, David Nelson <<a href="mailto:dnelson77808@gmail.com">dnelson77808@gmail.com</a>> wrote:<br
/>>><br/>>> I would like to add a running total column to the view, but can't quite get there. I figured
outhow to show the information I want using a CTE, but I can not figure out how to translate that to a single query to
definea view with. So I'm hoping someone can point me in the right direction.<br />>><br />><br />> -->
 <a
href="http://www.postgresql.org/docs/9.3/interactive/functions-window.html">http://www.postgresql.org/docs/9.3/interactive/functions-window.html</a><br
/>><br/>> David J.<br />>  <br /><br />Thanks David and Stuart!<br /><br />these two solutions seem to be what
I'mafter. I will apply them to the<br />production view (which is more complex), and see how they play out. But you<br
/>twopointed me in the directions that I needed to go.<br /><br />DROP VIEW upload_info;<br /><br />CREATE VIEW
upload_infoAS<br />SELECT mon,<br />       monthly_total,<br />       SUM(monthly_total) OVER (ORDER BY mon) AS
cumulative<br/>FROM (SELECT CONCAT(EXTRACT(YEAR FROM upload_date),<br />                    '-',<br
/>                   LPAD(CAST(EXTRACT(MONTH FROM upload_date) AS TEXT), 2, '0')) AS mon,<br />            
SUM(upload_size)AS monthly_total<br />      FROM uploaded_files u<br />      GROUP BY EXTRACT(MONTH FROM upload_date),
EXTRACT(YEARFROM upload_date)<br />      ORDER BY EXTRACT(YEAR FROM upload_date), EXTRACT(MONTH FROM upload_date)) AS
u;<br/><br />SELECT *<br />FROM upload_info;<br /><br />   mon   | monthly_total  |   cumulative   <br
/>---------+----------------+----------------<br/> 2014-01 |     3179135699 |     3179135699<br /> 2014-02 |    
1634499060|     4813634759<br /> 2014-03 |     4278982588 |     9092617347<br /> 2014-04 |   142238544857 |  
151331162204<br/> 2014-05 |   357240707209 |   508571869413<br /> 2014-06 |    64339859968 |   572911729381<br
/> 2014-07|    25513017728 |   598424747109<br /> 2014-08 |    11020669492 |   609445416601<br /> 2014-09 |    
1018774598|   610464191199<br /> 2014-10 |    76752535951 |   687216727150<br /> 2014-11 |     4611404964 |  
691828132114<br/> 2014-12 |    69607199452 |   761435331566<br /> 2015-01 |       15700525 |   761451032091<br
/> 2015-02|    34234715981 |   795685748072<br /> 2015-03 |  1484150449194 |  2279836197266<br /> 2015-04 |
13096208914706| 15376045111972<br /> 2015-05 |      814385166 | 15376859497138<br /> 2015-06 |      563829413 |
15377423326551<br/> 2015-07 |      656007272 | 15378079333823<br /> 2015-08 |  1828956674258 | 17207036008081<br
/> 2015-09|      822601322 | 17207858609403<br />(21 rows)<br /><br /><br /><br />DROP VIEW upload_info;<br /><br
/>CREATEVIEW upload_info AS<br />WITH upload_summary AS<br />(<br />   SELECT EXTRACT(YEAR FROM upload_date) AS
up_year,<br/>          LPAD(CAST(EXTRACT(MONTH FROM upload_date) AS TEXT), 2, '0') AS up_month,<br />         
SUM(upload_size)AS monthly_total<br />   FROM uploaded_files<br />   GROUP BY up_month, up_year<br />)<br />SELECT
u.up_year,<br/>       u.up_month,<br />       u.monthly_total,<br />       (SELECT SUM(monthly_total)<br />        FROM
upload_summary<br/>        WHERE CAST(CONCAT(up_year,<br />                          '-',<br
/>                         up_month,<br />                          '-01 00:00:00-05')<br />                   AS
TIMESTAMP)<=<br />              CAST(CONCAT(u.up_year,<br />                          '-',<br
/>                         u.up_month,<br />                          '-01 00:00:00-05')<br />                   AS
TIMESTAMP)<br/>       ) AS cumulative<br />FROM upload_summary u<br />ORDER BY up_year, up_month;<br /><br />SELECT
*<br/>FROM upload_info;<br /><br /> up_year | up_month | monthly_total  |   cumulative   <br
/>---------+----------+----------------+----------------<br/>    2014 | 01       |     3179135699 |     3179135699<br
/>   2014 | 02       |     1634499060 |     4813634759<br />    2014 | 03       |     4278982588 |     9092617347<br
/>   2014 | 04       |   142238544857 |   151331162204<br />    2014 | 05       |   357240707209 |   508571869413<br
/>   2014 | 06       |    64339859968 |   572911729381<br />    2014 | 07       |    25513017728 |   598424747109<br
/>   2014 | 08       |    11020669492 |   609445416601<br />    2014 | 09       |     1018774598 |   610464191199<br
/>   2014 | 10       |    76752535951 |   687216727150<br />    2014 | 11       |     4611404964 |   691828132114<br
/>   2014 | 12       |    69607199452 |   761435331566<br />    2015 | 01       |       15700525 |   761451032091<br
/>   2015 | 02       |    34234715981 |   795685748072<br />    2015 | 03       |  1484150449194 |  2279836197266<br
/>   2015 | 04       | 13096208914706 | 15376045111972<br />    2015 | 05       |      814385166 | 15376859497138<br
/>   2015 | 06       |      563829413 | 15377423326551<br />    2015 | 07       |      656007272 | 15378079333823<br
/>   2015 | 08       |  1828956674258 | 17207036008081<br />    2015 | 09       |      822601322 | 17207858609403<br
/>(21rows)<br /></div>