Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl (date_added timestamptz, qty int);
    
INSERT INTO tbl (date_added, qty) VALUES
  ('2015-08-04 22:28:24.633784-03', 1)
, ('2015-05-20 20:22:29.458541-03', 1)
, ('2015-04-08 14:16:09.844229-03', 1)
, ('2015-04-07 23:10:42.325081-03', 1)
, ('2015-07-06 18:50:30.164932-03', 1)
, ('2015-08-22 15:01:54.03697-03' , 1)
, ('2015-08-06 18:25:07.57763-03' , 1)
, ('2015-04-07 23:12:20.850783-03', 1)
, ('2015-07-23 17:45:29.456034-03', 1)
, ('2015-04-28 20:12:48.110922-03', 1)
, ('2015-04-28 13:26:04.770365-03', 1)
, ('2015-05-19 13:30:08.186289-03', 1)
, ('2015-08-06 18:26:46.448608-03', 1)
, ('2015-08-27 16:43:06.561005-03', 1)
, ('2015-08-07 12:15:29.242067-03', 1)
;

CREATE TABLE



INSERT 0 15


SELECT to_char(date_trunc('month', date_added), 'Mon YYYY') AS mon_text
     , sum(sum(qty)) OVER (ORDER BY date_trunc('month', date_added)) AS running_sum
FROM   tbl
GROUP  BY date_trunc('month', date_added)
ORDER  BY date_trunc('month', date_added);


mon_text
running_sum


Apr 2015
5

May 2015
7

Jul 2015
9

Aug 2015
15



SELECT 4


-- fill in missing months:
WITH cte AS (
   SELECT date_trunc('month', date_added) AS mon, sum(qty) AS mon_sum
   FROM   tbl
   GROUP  BY 1
   )
SELECT to_char(mon, 'Mon YYYY') AS mon_text
     , sum(c.mon_sum) OVER (ORDER BY mon) AS running_sum
FROM  (SELECT min(mon) AS min_mon FROM cte) init
     , generate_series(init.min_mon, now(), interval '1 month') mon
LEFT   JOIN cte c USING (mon)
ORDER  BY mon;


mon_text
running_sum


Apr 2015
5

May 2015
7

Jun 2015
7

Jul 2015
9

Aug 2015
15

Sep 2015
15

Oct 2015
15

Nov 2015
15

Dec 2015
15

Jan 2016
15

Feb 2016
15

Mar 2016
15

Apr 2016
15

May 2016
15

Jun 2016
15

Jul 2016
15

Aug 2016
15

Sep 2016
15

Oct 2016
15

Nov 2016
15

Dec 2016
15

Jan 2017
15

Feb 2017
15

Mar 2017
15

Apr 2017
15

May 2017
15

Jun 2017
15

Jul 2017
15

Aug 2017
15

Sep 2017
15

Oct 2017
15

Nov 2017
15

Dec 2017
15

Jan 2018
15

Feb 2018
15

Mar 2018
15

Apr 2018
15

May 2018
15

Jun 2018
15

Jul 2018
15

Aug 2018
15

Sep 2018
15

Oct 2018
15

Nov 2018
15

Dec 2018
15

Jan 2019
15

Feb 2019
15

Mar 2019
15

Apr 2019
15

May 2019
15

Jun 2019
15

Jul 2019
15

Aug 2019
15

Sep 2019
15

Oct 2019
15

Nov 2019
15

Dec 2019
15

Jan 2020
15

Feb 2020
15

Mar 2020
15

Apr 2020
15

May 2020
15

Jun 2020
15

Jul 2020
15

Aug 2020
15

Sep 2020
15

Oct 2020
15

Nov 2020
15

Dec 2020
15

Jan 2021
15

Feb 2021
15

Mar 2021
15

Apr 2021
15

May 2021
15

Jun 2021
15

Jul 2021
15

Aug 2021
15

Sep 2021
15

Oct 2021
15

Nov 2021
15

Dec 2021
15

Jan 2022
15

Feb 2022
15

Mar 2022
15

Apr 2022
15

May 2022
15

Jun 2022
15

Jul 2022
15

Aug 2022
15

Sep 2022
15

Oct 2022
15

Nov 2022
15

Dec 2022
15

Jan 2023
15

Feb 2023
15

Mar 2023
15

Apr 2023
15

May 2023
15

Jun 2023
15

Jul 2023
15

Aug 2023
15

Sep 2023
15

Oct 2023
15

Nov 2023
15

Dec 2023
15

Jan 2024
15



SELECT 106


-- start with given month
WITH cte AS (
   SELECT date_trunc('month', date_added) AS mon, sum(qty) AS mon_sum
   FROM   tbl
   GROUP  BY 1
   )
SELECT to_char(mon, 'Mon YYYY') AS mon_text
     , COALESCE(sum(c.mon_sum) OVER (ORDER BY mon), 0) AS running_sum
FROM   generate_series('2015-01-01'::date, now(), interval '1 month') mon
LEFT   JOIN cte c USING (mon)
ORDER  BY mon;


mon_text
running_sum


Jan 2015
0

Feb 2015
0

Mar 2015
0

Apr 2015
5

May 2015
7

Jun 2015
7

Jul 2015
9

Aug 2015
15

Sep 2015
15

Oct 2015
15

Nov 2015
15

Dec 2015
15

Jan 2016
15

Feb 2016
15

Mar 2016
15

Apr 2016
15

May 2016
15

Jun 2016
15

Jul 2016
15

Aug 2016
15

Sep 2016
15

Oct 2016
15

Nov 2016
15

Dec 2016
15

Jan 2017
15

Feb 2017
15

Mar 2017
15

Apr 2017
15

May 2017
15

Jun 2017
15

Jul 2017
15

Aug 2017
15

Sep 2017
15

Oct 2017
15

Nov 2017
15

Dec 2017
15

Jan 2018
15

Feb 2018
15

Mar 2018
15

Apr 2018
15

May 2018
15

Jun 2018
15

Jul 2018
15

Aug 2018
15

Sep 2018
15

Oct 2018
15

Nov 2018
15

Dec 2018
15

Jan 2019
15

Feb 2019
15

Mar 2019
15

Apr 2019
15

May 2019
15

Jun 2019
15

Jul 2019
15

Aug 2019
15

Sep 2019
15

Oct 2019
15

Nov 2019
15

Dec 2019
15

Jan 2020
15

Feb 2020
15

Mar 2020
15

Apr 2020
15

May 2020
15

Jun 2020
15

Jul 2020
15

Aug 2020
15

Sep 2020
15

Oct 2020
15

Nov 2020
15

Dec 2020
15

Jan 2021
15

Feb 2021
15

Mar 2021
15

Apr 2021
15

May 2021
15

Jun 2021
15

Jul 2021
15

Aug 2021
15

Sep 2021
15

Oct 2021
15

Nov 2021
15

Dec 2021
15

Jan 2022
15

Feb 2022
15

Mar 2022
15

Apr 2022
15

May 2022
15

Jun 2022
15

Jul 2022
15

Aug 2022
15

Sep 2022
15

Oct 2022
15

Nov 2022
15

Dec 2022
15

Jan 2023
15

Feb 2023
15

Mar 2023
15

Apr 2023
15

May 2023
15

Jun 2023
15

Jul 2023
15

Aug 2023
15

Sep 2023
15

Oct 2023
15

Nov 2023
15

Dec 2023
15

Jan 2024
15



SELECT 109

mon_text	running_sum
Apr 2015	5
May 2015	7
Jun 2015	7
Jul 2015	9
Aug 2015	15
Sep 2015	15
Oct 2015	15
Nov 2015	15
Dec 2015	15
Jan 2016	15
Feb 2016	15
Mar 2016	15
Apr 2016	15
May 2016	15
Jun 2016	15
Jul 2016	15
Aug 2016	15
Sep 2016	15
Oct 2016	15
Nov 2016	15
Dec 2016	15
Jan 2017	15
Feb 2017	15
Mar 2017	15
Apr 2017	15
May 2017	15
Jun 2017	15
Jul 2017	15
Aug 2017	15
Sep 2017	15
Oct 2017	15
Nov 2017	15
Dec 2017	15
Jan 2018	15
Feb 2018	15
Mar 2018	15
Apr 2018	15
May 2018	15
Jun 2018	15
Jul 2018	15
Aug 2018	15
Sep 2018	15
Oct 2018	15
Nov 2018	15
Dec 2018	15
Jan 2019	15
Feb 2019	15
Mar 2019	15
Apr 2019	15
May 2019	15
Jun 2019	15
Jul 2019	15
Aug 2019	15
Sep 2019	15
Oct 2019	15
Nov 2019	15
Dec 2019	15
Jan 2020	15
Feb 2020	15
Mar 2020	15
Apr 2020	15
May 2020	15
Jun 2020	15
Jul 2020	15
Aug 2020	15
Sep 2020	15
Oct 2020	15
Nov 2020	15
Dec 2020	15
Jan 2021	15
Feb 2021	15
Mar 2021	15
Apr 2021	15
May 2021	15
Jun 2021	15
Jul 2021	15
Aug 2021	15
Sep 2021	15
Oct 2021	15
Nov 2021	15
Dec 2021	15
Jan 2022	15
Feb 2022	15
Mar 2022	15
Apr 2022	15
May 2022	15
Jun 2022	15
Jul 2022	15
Aug 2022	15
Sep 2022	15
Oct 2022	15
Nov 2022	15
Dec 2022	15
Jan 2023	15
Feb 2023	15
Mar 2023	15
Apr 2023	15
May 2023	15
Jun 2023	15
Jul 2023	15
Aug 2023	15
Sep 2023	15
Oct 2023	15
Nov 2023	15
Dec 2023	15
Jan 2024	15

mon_text	running_sum
Jan 2015	0
Feb 2015	0
Mar 2015	0
Apr 2015	5
May 2015	7
Jun 2015	7
Jul 2015	9
Aug 2015	15
Sep 2015	15
Oct 2015	15
Nov 2015	15
Dec 2015	15
Jan 2016	15
Feb 2016	15
Mar 2016	15
Apr 2016	15
May 2016	15
Jun 2016	15
Jul 2016	15
Aug 2016	15
Sep 2016	15
Oct 2016	15
Nov 2016	15
Dec 2016	15
Jan 2017	15
Feb 2017	15
Mar 2017	15
Apr 2017	15
May 2017	15
Jun 2017	15
Jul 2017	15
Aug 2017	15
Sep 2017	15
Oct 2017	15
Nov 2017	15
Dec 2017	15
Jan 2018	15
Feb 2018	15
Mar 2018	15
Apr 2018	15
May 2018	15
Jun 2018	15
Jul 2018	15
Aug 2018	15
Sep 2018	15
Oct 2018	15
Nov 2018	15
Dec 2018	15
Jan 2019	15
Feb 2019	15
Mar 2019	15
Apr 2019	15
May 2019	15
Jun 2019	15
Jul 2019	15
Aug 2019	15
Sep 2019	15
Oct 2019	15
Nov 2019	15
Dec 2019	15
Jan 2020	15
Feb 2020	15
Mar 2020	15
Apr 2020	15
May 2020	15
Jun 2020	15
Jul 2020	15
Aug 2020	15
Sep 2020	15
Oct 2020	15
Nov 2020	15
Dec 2020	15
Jan 2021	15
Feb 2021	15
Mar 2021	15
Apr 2021	15
May 2021	15
Jun 2021	15
Jul 2021	15
Aug 2021	15
Sep 2021	15
Oct 2021	15
Nov 2021	15
Dec 2021	15
Jan 2022	15
Feb 2022	15
Mar 2022	15
Apr 2022	15
May 2022	15
Jun 2022	15
Jul 2022	15
Aug 2022	15
Sep 2022	15
Oct 2022	15
Nov 2022	15
Dec 2022	15
Jan 2023	15
Feb 2023	15
Mar 2023	15
Apr 2023	15
May 2023	15
Jun 2023	15
Jul 2023	15
Aug 2023	15
Sep 2023	15
Oct 2023	15
Nov 2023	15
Dec 2023	15
Jan 2024	15