Postgres: How can you select last 12 months only, and return total count of each month, even with null/empty months?

Question

I'm using Postgres, and my dataset is an orders table. I'm trying to learn how to show the 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 it's not resource intensive and fast.

• nbk: Please add a minimal reproducible example to your qiestion
• estransconder: @nbk its already there. read rather than downvote and comment meaningless stuff.
• nbk: a ]mre] is clearly, that the result set expected, must have a source table(s) as it is stated in the provided link.
• estransconder: as per my original message, its in the problem. It's Total_orders. Already solved. Thanks. @nbk
• Vérace: Hii, and welome to the forum! I think that your question is fair enough - although some people seem to be too quick off the mark to downvote and/or vote to close! As a New Contributor, you should have been cut some slack. Just a couple of pointers: 1) **Always include your server version -with your question, either as a tag or clearly at the beginnning 2) When you provide table data, you should also include it as a fiddle - i.e with DDL and DML - db.fiddle.uk is generally considered the best - this helps those trying to help you!

Answer 1

One option is to create a date dimensions table which you can JOIN on for various date dependent goals such as this one. The date dimensions table typically has useful fields describing a specific date in time such as DateOf, DayOf, MonthOf, YearOf, DayOfWeek, IsHoliday, IsWeekend, etc. But for this specific case, you'd just simply use the MonthOf and YearOf fields in a LEFT JOIN from the date dimensions table like so:

SELECT
 CONCAT(DD.YearOf, '-', DD.MonthOf, '-01 00:00:00') AS total_orders,
 COALESCE(COUNT(O.id), 0) AS count
FROM DateDimensions DD
LEFT JOIN orders O
 DD.DateOf = O.created_at
WHERE O.id = 1
GROUP BY DD.YearOf, DD.MonthOf

Alternatively I believe you can generate a date series with the generate_series function in PostgreSQL too, but I'm not super versed on how to do so. This DBA.StackExchange answer has a lot of good information on generating date series.

Answer 2

Found this solution that works great!

select date(d) as day, count(sales.id) 
from generate_series(
 current_date - interval '30 day', 
 current_date, 
 '1 day'
) d 
left join sales on date(sales.created_at) = d 
group by day order by day;

https://www.sisense.com/blog/use-generate-series-to-get-continuous-results/

This article answers the question for any future lurkers!