VladSun,
I used HAVING and COALESCE as you suggested, like this:
Code: Select all
SELECT
peerReport.realm,
sum(IF(peerReport.summary_date BETWEEN '2009-07-24' AND '2009-07-29',peerReport.mou,0)) AS TOTAL,
sum(IF(peerReport.summary_date = '2009-07-29',peerReport.mou,NULL)) AS '2009-07-29',
sum(IF(peerReport.summary_date = '2009-07-28',peerReport.mou,NULL)) AS '2009-07-28',
sum(IF(peerReport.summary_date = '2009-07-27',peerReport.mou,NULL)) AS '2009-07-27',
sum(IF(peerReport.summary_date = '2009-07-26',peerReport.mou,NULL)) AS '2009-07-26',
sum(IF(peerReport.summary_date = '2009-07-25',peerReport.mou,NULL)) AS '2009-07-25',
sum(IF(peerReport.summary_date = '2009-07-24',peerReport.mou,NULL)) AS '2009-07-24'
FROM
peerReport
WHERE
peerReport.tab = 'termination'
GROUP BY
peerReport.realm
WITH ROLLUP
HAVING COALESCE(
sum(IF(peerReport.summary_date = '2009-07-29',peerReport.mou,NULL)),
sum(IF(peerReport.summary_date = '2009-07-28',peerReport.mou,NULL)),
sum(IF(peerReport.summary_date = '2009-07-27',peerReport.mou,NULL)),
sum(IF(peerReport.summary_date = '2009-07-26',peerReport.mou,NULL)),
sum(IF(peerReport.summary_date = '2009-07-25',peerReport.mou,NULL)),
sum(IF(peerReport.summary_date = '2009-07-24',peerReport.mou,NULL))
)
LIMIT 0, 300;
It works great,
I also tried removing the HAVING COALESCE and adding
Code: Select all
peerReport.summary_date BETWEEN '2009-07-24' AND '2009-07-29'
Both work great. Hard to tell which one is faster as both are quite fast.
I also added WITH ROLLUP, to give sums across the bottom row, as well as:
Code: Select all
sum(IF(peerReport.summary_date BETWEEN '2009-07-24' AND '2009-07-29',peerReport.mou,0)) AS TOTAL
Which gives sums for each realm across the specified date range.
I'll experiment with both versions, and post some details here.
Thanks again for all your help.
--McMurphy