I am working with PSQL/pgAdmin, looking to get a total # of inventory grouped by month for the year. I've tried a generated_series, but when I try to use a Count Filter it seems to ignore the date.
My attempt
select
g.day::date,
count(item) filter(
where install_date <= g.day+'1month'::interval
and (removed_date is null or removed_date < g.day+'1month'::interval))
FROM table
CROSS JOIN generate_series('2024-01-01'::timestamp, '2025-01-01'::timestamp, '1 month'::interval) AS g(day)
group by 1,2
As an example:
Item | Install_Date | Removed_Date |
---|---|---|
Item1 | Jan 2024 | Mar 2024 |
Item3 | Feb 2024 | Apr 2024 |
So the results would look something like
Month | Install_Count |
---|---|
Jan | 1 |
Feb | 2 |
Mar | 1 |
Apr | 0 |
All months are generated within a date range.
Each month is then compared with install_date
and removed_date should be greater than the month_start so that it is excluded from that month.
WITH month_series AS (
SELECT generate_series('2024-01-01'::date, '2024-12-01'::date, '1 month'::interval) AS month_start
)
SELECT
to_char(month_series.month_start, 'Mon YYYY') AS month,
COUNT(i.item) AS install_count
FROM
month_series
LEFT JOIN
inventory i
ON i.install_date <= month_series.month_start
AND (i.removed_date IS NULL OR i.removed_date > month_series.month_start)
GROUP BY
month_series.month_start
ORDER BY
month_series.month_start;
Output
month | install_count |
---|---|
Jan 2024 | 1 |
Feb 2024 | 2 |
Mar 2024 | 1 |
Apr 2024 | 0 |
May 2024 | 0 |
Jun 2024 | 0 |
Jul 2024 | 0 |
Aug 2024 | 0 |
Sep 2024 | 0 |
Oct 2024 | 0 |
Nov 2024 | 0 |
Dec 2024 | 0 |