CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
khairul
khairul

Reputation: 1260

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

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

Upvotes: 4

Views: 7311

Answers (2)

Khalid Amin
Khalid Amin

Reputation: 902

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.

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425241

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

Upvotes: 7

Related Questions