How to give default values for the SUM() and COUNT() if no data exist in MySQL query which uses GROUP BY?

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(DISTINCTq.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

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 have JOIN clauses
• 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.

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