MySQL: Return 0 if row doen't exist

Question

I've been bashing my head on this for a while, so now I'm here :) I'm a SQL beginner, so maybe this will be easy for you guys...

I have this query:

SELECT COUNT(*) AS counter, recur,subscribe_date  
FROM paypal_subscriptions  
WHERE recur='monthly' and subscribe_date > "2010-07-16" and subscribe_date < "2010-07-23"  
GROUP BY subscribe_date  
ORDER BY subscribe_date  

Now the dates I've shown above are hard coded, my application will supply a variable date range.

Right now I'm getting a result table where there is a value for that date.

counter |recur | subscribe_date   
2    |    Monthly | 2010-07-18  
3    |    Monthly | 2010-07-19  
4    |    Monthly | 2010-07-20    
6    |    Monthly | 2010-07-22

I'd like to return in the counter column if the date doesn't exist.

counter |recur | subscribe_date   
0    |    Monthly | 2010-07-16  
0    |    Monthly | 2010-07-17  
2    |    Monthly | 2010-07-18  
3    |    Monthly | 2010-07-19  
4    |    Monthly | 2010-07-20    
0    |    Monthly | 2010-07-21  
6    |    Monthly | 2010-07-22  
0    |    Monthly | 2010-07-23 

Is this possible?

Answer 1

You will need a table of dates to group against. This is quite easy in MSSQL using CTE's like this - I'm not sure if MySQL has something similar? Otherwise you will need to create a hard table as a one off exercise

EDIT : Give this a try:

SELECT COUNT(pp.subscribe_date) AS counter, dl.date, MIN(pp.recur)
FROM date_lookup dl 
    LEFT OUTER JOIN paypal pp 
    on (pp.subscribe_date = dl.date AND pp.recur ='monthly') 
WHERE dl.date >= '2010-07-16' and dl.date <= '2010-07-23'
GROUP BY dl.date
ORDER BY dl.date

• The subject of the query needs to be changed to the date_lookup table (the order of the Left Outer Join becomes important)
• Count(*) isn't going to work since the 'date' record always exists - need to count something in the PayPay table
• pp.recur ='monthly' is now a join condition, not a filter because of the LOJ

Finally, showing pp.recur in the select list isn't going to work.

I've used an aggregate, but MIN(pp.recur) will return null if there are no PayPal records

What you could do when you parameterize your query is to just repeat the Recur Type Filter? Again, plz excuse the MSSQL syntax

SELECT COUNT(pp.subscribe_date) AS counter, dl.date, @ppRecur
FROM date_lookup dl 
    LEFT OUTER JOIN paypal pp 
    on (pp.subscribe_date = dl.date AND pp.recur =@ppRecur) 
WHERE dl.date >= @DateFrom and dl.date <= @DateTo
GROUP BY dl.date
ORDER BY dl.date

Answer 2

You will need a table of dates (new table added), and then you will have to do an outer join between that table and your query.

This question is also similar to another question. Answers can be quite similar.

http://stackoverflow.com/questions/373490/insert-dates-in-the-return-from-a-query-where-there-is-none/373734#373734

Answer 3

Since there was no easy way to do this, I had to have the application fill in the blanks for me rather than have the database return the data I wanted. I do get a performance hit for this, but it was necessary for the completion of the report.

I will definitely look into making this return what I want from the DB in the near future. I'll give nonnb's solution a try.

thanks everyone!