Creating cumulative sums by combining aggregation with windowing   

The sample data

Let's start by creating a table with some sales data.

CREATE TABLE sales(id integer GENERATED ALWAYS AS IDENTITY,
		 product varchar(100),
		order_date date, quantity integer, 
		CONSTRAINT pk_sales PRIMARY KEY(id) 
		);
INSERT INTO sales(product, order_date, quantity)
WITH d AS( SELECT f.product, d.ts::date, 
	mod(date_part('epoch', d.ts)::integer, f.ord + 73) AS quantity
   FROM unnest(ARRAY['pen', 'book']) WITH ORDINALITY AS f(product,ord)
	 CROSS JOIN generate_series('2021-11-01'::timestamp, 
			'2022-01-07'::timestamp, interval '1.2 hours') AS d(ts)
   WHERE mod(date_part('epoch', d.ts)::integer,7) = 1
         )
SELECT *
FROM d WHERE quantity > 0;

Monthly sum

A basic monthly sum would look like this:

SELECT to_char(order_date, 'YYYY-MM') AS month, 
    product,  
    SUM(quantity)  As month_sales
FROM sales
GROUP BY product, month
ORDER BY product, month;

The Output of the above is:

  month  | product | month_sales
---------+---------+-------------
 2021-11 | book    |        2550
 2021-12 | book    |        2640
 2022-01 | book    |         510
 2021-11 | pen     |        3076
 2021-12 | pen     |        3134
 2022-01 | pen     |         614
(6 rows)

Cumulative Sum

But what if you want a sales to date as of the end of the month in addition to the monthly sum. How do you include that as an additional column?

SELECT to_char(order_date, 'YYYY-MM') AS month, 
    product,  
    SUM(quantity)  As month_sales, 
	SUM(SUM(quantity)) 
	  OVER(PARTITION BY product 
		ORDER BY to_char(order_date, 'YYYY-MM')) AS sales_to_date
FROM sales
GROUP BY product, month
ORDER BY product, month;

Which yields

  month  | product | month_sales | sales_to_date
---------+---------+-------------+---------------
 2021-11 | book    |        2550 |          2550
 2021-12 | book    |        2640 |          5190
 2022-01 | book    |         510 |          5700
 2021-11 | pen     |        3076 |          3076
 2021-12 | pen     |        3134 |          6210
 2022-01 | pen     |         614 |          6824
(6 rows)