How to give default values for the sum, count if no data exist in mysql query which uses group by?

Question

I am using following query which works fine for me except one problem

SELECT f.period as month,
       sum(p.revenue * ((100-q.rate)/100)) as revenue,
       count(distinct q.label) as tot_stmt 
  FROM files f, reports p, rates q,albums a
 WHERE f.period in ('2010-06-01','2010-05-01','2010-04-01','2010-03-01') 
   AND f.period_closed = true
   AND q.period = f.period
   AND a.id = q.album_id
   AND p.file_id = f.id
   AND p.upc = a.upc
   AND p.revenue is not null
 GROUP BY month ORDER BY month DESC

O/P =>
month            revenue     tot_stmt

2010-06-01     10.00         2
2010-05-01     340.47       2

I want result like following

month            revenue     tot_stmt

2010-06-01     10.00         2
2010-05-01     340.47       2
2010-04-01     0.00           0
2010-03-01     0.00           0

Answer 1

You will need a helper table with the information you need. The helper table will hold months you are referring to:

month

2010-06-01 2010-05-01 2010-04-01 2010-03-01

Once you have that table, you can left outer join to it, and use isnull function to populate default values you want.

Something like:

SELECT f.period as month, 
       sum(p.revenue * ((100-q.rate)/100)) as revenue, 
       count(distinct q.label) as tot_stmt  
  FROM (files f left outer join periods pers on f.period = pers.period), reports p, rates q,albums a 
 WHERE f.period in ('2010-06-01','2010-05-01','2010-04-01','2010-03-01')  
   AND f.period_closed = true 
   AND q.period = f.period 
   AND a.id = q.album_id 
   AND p.file_id = f.id 
   AND p.upc = a.upc 
   AND p.revenue is not null 
 GROUP BY month ORDER BY month DESC 

Answer 2

SELECT f.period as month,
       ifnull(sum(p.revenue * ((100-q.rate)/100)),0) as revenue,
       count(distinct q.label) as tot_stmt 
  FROM files f 
       LEFT JOIN reports p ON f.id = p.file_id
       LEFT JOIN rates q ON f.period = q.period
       LEFT JOIN albums a ON q.album_id = a.id AND p.upc = a.upc
 WHERE f.period in ('2010-06-01','2010-05-01','2010-04-01','2010-03-01') 
       AND f.period_closed = true
 GROUP BY month ORDER BY month DESC

Explanations:
rewritten the conditions to be joins
count(column) returns 0 if all aggregated records have value NULL in that column
sum(column) returns NULL if all aggregated records have value NULL in that column
you also need to allow p.revenue to be NULL (dropped that criteria)

Note:
You don't seem to be getting anything from the albums table so you can take it out