Edit Question - KnowledgeHub

Title Body

I want to count ID's per month using generate_series() . This query works in PostgreSQL 9.1: SELECT (to_char(serie,'yyyy-mm')) AS year, sum(amount)::int AS eintraege FROM ( SELECT COUNT(mytable.id) as amount, generate_series::date as serie FROM mytable RIGHT JOIN generate_series( (SELECT min(date_from) FROM mytable)::date, (SELECT max(date_from) FROM mytable)::date, interval '1 day') ON generate_series = date(date_from) WHERE version = 1 GROUP BY generate_series ) AS foo GROUP BY Year ORDER BY Year ASC; This is my output: "2006-12" | 4 "2007-02" | 1 "2007-03" | 1 But what I want to get is this output ('0' value in January): "2006-12" | 4 "2007-01" | 0 "2007-02" | 1 "2007-03" | 1 Months without id should be listed nevertheless. Any ideas how to solve this? Sample data: drop table if exists mytable; create table mytable(id bigint, version smallint, date_from timestamp); insert into mytable(id, version, date_from) values (4084036, 1, '2006-12-22 22:46:35'), (4084938, 1, '2006-12-23 16:19:13'), (4084938, 2, '2006-12-23 16:20:23'), (4084939, 1, '2006-12-23 16:29:14'), (4084954, 1, '2006-12-23 16:28:28'), (4250653, 1, '2007-02-12 21:58:53'), (4250657, 1, '2007-03-12 21:58:53') ;

Tags (comma-separated) Cancel