sql - 在 group by 子句中返回空值 '0'，postgresql

Question

我正在使用 count 和 group by 来了解每天有多少订户进行购买:

select count(s.email) 
from subscriptions s, orders o 
where o.subcription_id=s.id 
group by s.created_at

结果:

created_at  count
------------------
04-04-2011  1
06-04-2011  2
07-04-2011  1
10-04-2011  5
11-04-2011  9

但是我仍然希望空行返回为“0”。我该怎么做呢？请注意，我必须使用两个表格。

created_at  count
------------------
04-04-2011  1
05-04-2011  0
06-04-2011  2
07-04-2011  1
08-04-2011  0
09-04-2011  0
10-04-2011  5
11-04-2011  9

Answer 1

SELECT  s.created_at, COUNT(o.subsription_id)
FROM    subscriptions s
LEFT JOIN
        orders o
ON      o.subcription_id = s.id
GROUP BY
        s.created_at