grocery-manager/scratch.sql

--Monthly report, breakdown by group
SELECT year, month, "group", total_price FROM (
	SELECT
		groups.name AS "group",
		date_part('year',ts) AS year,
		date_part('month', ts) AS month,
		units.name AS unit,
		TRUNC(sum(price),2) AS total_price
	FROM transactions AS t
	JOIN products ON (product_id = products.id)
	JOIN categories ON (category_id = categories.id)
	JOIN groups ON (group_id = groups.id)
	JOIN units ON (unit_id = units.id)
	WHERE date_part('year',ts) IN (2021, 2022)
	GROUP BY
		date_part('year',ts),
		ROLLUP(groups.name, date_part('month', ts), units.name)
	ORDER BY year, month, "group", unit
) AS subq WHERE unit IS NULL AND month IS NOT NULL AND "group" IS NOT NULL;

--Group monthly trend
SELECT * FROM (SELECT groups.name AS "group", date_part('year',ts) AS year, date_part('month', ts) AS month, TRUNC(sum(price),2) AS total_price FROM transactions AS t JOIN products ON (product_id = products.id) JOIN categories ON (category_id = categories.id) JOIN groups ON (group_id = groups.id) JOIN units ON (unit_id = units.id) WHERE date_part('year',ts) IN (2021, 2022) GROUP BY ROLLUP(groups.name, date_part('year', ts), date_part('month', ts)) ORDER BY "group",year, month) AS subq WHERE month IS NOT NULL;