Geek Answers Handbook

Returns null as '0' in group by, postgresql

I use count and group by to find out how many subscribers shop 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 strings to be returned as '0'. How can I do it? Please note that I need to use both 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
+4
source share
2 answers
 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
+7
source
Function

count () always returns a value (even if it is zero), the problem is that in one of your tables there are no corresponding rows. You need to do an outer join to include rows from both tables. Check which table has no rows, then add an outer join to include all rows from another 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, send the data of your tables.

+2
source

More articles:


All Articles