Creating cumulative sums by combining aggregation with windowing

When you want to include a running sum for each line of data in your query, you generally use a window clause with SUM. What if you don't want to list all line items of your data. You want a report that gives you a weekly sum and another that gives you the running sum for the whole year. What do you do then? We'll demonstrate how to do that.

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)