Attach the count request to the series_generation in postgres and also get the Null values ​​as "0",

Question

What I want to get is the statistics with each month from generate_series and the sum of the calculated identifier in each month. This SQL 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; 

And this is my conclusion

 "2006-12" | 4 "2007-02" | 1 "2007-03" | 1 

But I want to get this output (value "0" in January):

 "2006-12" | 4 "2007-01" | 0 "2007-02" | 1 "2007-03" | 1 

So, if there is a month without id, it should be specified, however. Any ideas how to solve this?

Here are some sample data:

 drop table if exists mytable; create table mytable(id bigint, version smallint, date_from timestamp without time zone); 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') ; 

Answer 1

Confusing, simplified, and fixed, it might look like this:

 SELECT to_char(s.tag,'yyyy-mm') AS monat ,count(t.id) AS eintraege FROM ( SELECT generate_series(min(date_from)::date ,max(date_from)::date ,interval '1 day' )::date AS tag FROM mytable t ) s LEFT JOIN mytable t ON t.date_from::date = s.tag AND t.version = 1 GROUP BY 1 ORDER BY 1; 

Among all the noises, misleading identifiers and an unconventional format, the real problem was hidden here:

 WHERE version = 1 

With the right use of the RIGHT JOIN you canceled the effort by adding a WHERE that requires a different value from mytable - effectively converting the RIGHT JOIN to JOIN .

Pull the sentence into JOIN state to do this job.

I simplified a couple more things.