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?

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 08: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 08: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 08: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 09:27
@AgmLauncher While it may be not possible in MySQL it is possible in Postgres. — Ihor Romanchenko, Jul 23 '13 at 11:01

Erwin Brandstetter · Accepted Answer · 2013-07-23T11:11:17.843

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.

score 1 · Answer 2 · answered Jul 23 '13 at 08:54

1

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

answered Jul 23 '13 at 08:54

Bora

10,529
5
43
73

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 08:57
Did you try num rows function after query result? $result = pg_query("QUERY"); pg_num_rows($result); [Doc](http://php.net/manual/en/function.pg-num-rows.php) – Bora Jul 23 '13 at 09:01

score 0 · Answer 3 · edited Sep 17 '18 at 15:50

0

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

edited Sep 17 '18 at 15:50

S-Man

22,521
7
40
63

answered Jul 23 '13 at 08:46

valex

23,966
7
43
60

score 0 · Answer 4 · answered Jul 23 '13 at 08:47

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

Retrieving row count and returning 0 when no rows

4 Answers4

Linked