I’m using Postgres, and my dataset is a orders table. I am trying to learn how to show total amount of orders per month, per user (assuming there’s only one user in this case).
My dataset:
id, user_id, total, created_at
My current query:
SELECT
DATE_TRUNC('month',created_at)
AS total_orders,
COUNT(id) AS count
FROM orders
WHERE id = 1
GROUP BY DATE_TRUNC('month',created_at);
This returns:
Total orders | count
2021-01-01 00:00:00, 1
However, here’s the tricky part (to me at least) – i am using this to generate some graphs. Specifically, the last 12 months. So i would like the query to return the last 12 months, and include 0 for when no orders were created. So this is expected output:
Total orders | count
2021-04-01 00:00:00, 0
2021-03-01 00:00:00, 0
2021-02-01 00:00:00, 0
2021-01-01 00:00:00, 1
2020-12-01 00:00:00, 0
2020-11-01 00:00:00, 0
2020-10-01 00:00:00, 0
2020-09-01 00:00:00, 0
2020-08-01 00:00:00, 0
2020-07-01 00:00:00, 0
2020-06-01 00:00:00, 0
2020-05-01 00:00:00, 0
2020-04-01 00:00:00, 0
How can i accomplish this? the ultimate goal is to be super lightweight so that its not resource intensive and fast.
