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