Retrieving row count and returning 0 when no rows

Question

I've written a query to retrieve how many website signups I've had every day:

SELECT created, COUNT(id)
FROM signups
GROUP BY created
ORDER BY created desc

However, this only retrieves rows for days where people have actually signed up. If nobody has signed up in a day, I would like to return 0 for that day. Is there a way to do this using SQL or will I have to parse through the results using PHP?

Answer 1

You should create a calendar table in your database (or generate it in a query) and join it with yours then you will get 0 for empty days

SELECT calendar.c_date, COUNT(signups.id)
FROM calendar
left join signups on calendar.c_date=signups.created

GROUP BY c_date
ORDER BY c_date desc

Here is a way to make a calendar date in PostgreSQL

Answer 2

You need to make use of a calendar table that has a series of dates and join with it

select cal.created,coalesce(total) as total from calender_table as cal left join
(
SELECT created, COUNT(id) as total
FROM signups
GROUP BY created
) as source on cal.created=source.created
ORDER BY cal.created desc

Answer 3

You can use NULLIF function:

   SELECT created, NULLIF(COUNT(id), 0)
     FROM signups
 GROUP BY created
 ORDER BY created desc

Documentation: http://www.postgresql.org/docs/8.1/static/functions-conditional.html

Answer 4

Assuming created to be of type date for lack of information.

Postgres provides the wonderful generate_series() to make this easy:

SELECT d.created, COUNT(s.id) AS ct
FROM  (
   SELECT generate_series(min(created)
                        , max(created), interval '1 day')::date AS created
   FROM   signups
   ) d
LEFT   JOIN signups s USING (created)
GROUP  BY 1
ORDER  BY 1 DESC;

This retrieves minimum and maximum day from your table automatically and provides one row per day in between.