Take the 2-minute tour ×

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?

share|improve this question
if no one signs up there is no created record for that day so it will not display a count for that day. –  DevZer0 Jul 23 '13 at 8:43
What DevZero said, it is not possible for MySQL to infer information it doesn't have. You will have to parse through it using PHP to insert missing days with a count of 0. –  AgmLauncher Jul 23 '13 at 8:45
@DevZer0, yes, that's the problem. I was wondering if there was a way to "loop" and insert missing dates with the value of 0. Looks like there isn't. –  James Dawson Jul 23 '13 at 8:46
You forgot to provide the table definition with the data types of your columns and your version of Postgres. –  Erwin Brandstetter Jul 23 '13 at 9:27
@AgmLauncher While it may be not possible in MySQL it is possible in Postgres. –  Igor Romanchenko Jul 23 '13 at 11:01

4 Answers 4

up vote 3 down vote accepted

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
   SELECT generate_series(min(created)
                        , max(created), interval '1 day')::date AS created
   FROM   signups
   ) d
LEFT   JOIN signups s USING (created)

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

share|improve this answer
Perfect, thank you! :) –  James Dawson Jul 28 '13 at 21:40

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

share|improve this answer
That doesn't work because if there are no records in signups for that day there's nothing being returned. –  James Dawson Jul 23 '13 at 8:57
Did you try num rows function after query result? $result = pg_query("QUERY"); pg_num_rows($result); Doc –  Bora Jul 23 '13 at 9:01

You should create a calendar table in your DB (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 PostgreeSQL

share|improve this answer

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
share|improve this answer

Your Answer


