如何把series的值赋值为0_sql – 在postgres中的generate_series上加入计数查询,并将Null值检索为“0”...

我想得到的是每月来自generate_series的统计数据以及每个月中计算的id的总和.这个SQL适用于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;

这是我的输出

"2006-12" | 4

"2007-02" | 1

"2007-03" | 1

但我想得到的是这个输出(1月份的“0”值)：

"2006-12" | 4

"2007-01" | 0

"2007-02" | 1

"2007-03" | 1

因此,如果有一个没有id的月份,它应该被列出.

任何想法如何解决这个问题？

以下是一些示例数据：

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')

;