How to give default values for the sum, count if no data exist in mysql query which uses group by?
- by Salil
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
Regards,
Salil Gaikwad
