Return null value as '0' in group by clause, postgresql

Question

I am using count and group by to find out how many subscribers make a purchase every day:

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

Results:

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

However I still want the null rows to return as '0'. How do I do this? Do take note that I have to use both of the tables.

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

Answer 2

count() function always returns a value (even if it is zero), the problem is that one of your tables is missing corresponding rows. You need to perform an outer join to include rows from both tables. Please check which table is missing the rows, then place an outer join to include all rows from the other table.

SELECT s.created_at, COUNT(o.subscription_id)
FROM   subscriptions s
LEFT OUTER JOIN order o //(Depending on situation, it can be RIGHT OUTER JOIN)
ON (s.id = o.subscription_id)
GROUP BY s.created_at

If you still have problems, please post data of your tables.