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)
returns0
if all aggregated records have valueNULL
in that columnSUM(column)
returnsNULL
if all aggregated records have valueNULL
in that column- you also need to allow
p.revenue
to beNULL
(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
精彩评论