Well, to not include them into result set it's better to limit result before grouping, by specifying the date range that is retrieved. Then "empty" rows will disappear.VladSun wrote:By "no list of "empty" rows" I meant that rows like:Weirdan wrote:well, that only requires a join to the table where the full list of those 'realms' is stored.VladSun wrote:Well, in both "versions" the OP requirements are still not met (no list of "empty" rows)
realm NULL NULL NULL
or
realm 0 0 0
must not be listed in the result, that's why I suggested using HAVING clause.
Solutions suggested till now list all of the realms, including "all-zeroes" rows
MySQL: SELF JOIN, using dates as columns
Moderator: General Moderators
Re: MySQL: SELF JOIN, using dates as columns
Re: MySQL: SELF JOIN, using dates as columns
Weirdan wrote:Well, to not include them into result set it's better to limit result before grouping, by specifying the date range that is retrieved. Then "empty" rows will disappear.
You can't use aggregated fields in the WHERE clause, you have to use them in the HAVING clause.
There are 10 types of people in this world, those who understand binary and those who don't
Re: MySQL: SELF JOIN, using dates as columns
I think weirdan meant something like
Which would filter the rows that don't have a summary date in the range of the report
Code: Select all
WHERE peerReport.tab = 'term' AND peerReport.summary_date BETWEEN '2009-06-24' AND '2009-06-26'Re: MySQL: SELF JOIN, using dates as columns
pytrin wrote:I think weirdan meant something likeWhich would filter the rows that don't have a summary date in the range of the reportCode: Select all
WHERE peerReport.tab = 'term' AND peerReport.summary_date BETWEEN '2009-06-24' AND '2009-06-26'
Last edited by VladSun on Fri Jul 31, 2009 8:58 am, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
Re: MySQL: SELF JOIN, using dates as columns
Sure. Here it is:VladSun wrote:Weirdan wrote:Well, to not include them into result set it's better to limit result before grouping, by specifying the date range that is retrieved. Then "empty" rows will disappear.Example, please? I couldn't get the "by specifying the date range that is retrieved" part.
Code: Select all
SELECT
peerReport.realm,
sum(
IF(peerReport.summary_date = '2009-06-24',
peerReport.mou,
NULL
) AS '2009-06-24',
sum(
IF(peerReport.summary_date = '2009-06-25',
peerReport.mou,
NULL
) AS '2009-06-25',
sum(
IF(peerReport.summary_date = '2009-06-26',
peerReport.mou,
NULL
) AS '2009-06-26'
FROM
peerReport
WHERE
peerReport.tab = 'term'
AND peerReport.summary_date BETWEEN '2009-06-24' AND '2009-06-26' -- <==== this is what I was talking about
GROUP BY
peerReport.realm
Re: MySQL: SELF JOIN, using dates as columns
Yeah ... I finally got it!
I think I should turn on the air conditioning
PS: This LEFT JOIN is to be blamed for everything
Last edited by VladSun on Fri Jul 31, 2009 9:14 am, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
Re: MySQL: SELF JOIN, using dates as columns
The [sql]BETWEEN '2009-06-24' AND '2009-06-26'[/sql] will also speed up the query, IMHO
There are 10 types of people in this world, those who understand binary and those who don't
-
mcmurphy510
- Forum Newbie
- Posts: 6
- Joined: Tue Jul 28, 2009 12:13 pm
Re: MySQL: SELF JOIN, using dates as columns
VladSun,
I used HAVING and COALESCE as you suggested, like this:
It works great,
I also tried removing the HAVING COALESCE and adding
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:
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
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;I also tried removing the HAVING COALESCE and adding
Code: Select all
peerReport.summary_date BETWEEN '2009-07-24' AND '2009-07-29'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 TOTALI'll experiment with both versions, and post some details here.
Thanks again for all your help.
--McMurphy
-
mcmurphy510
- Forum Newbie
- Posts: 6
- Joined: Tue Jul 28, 2009 12:13 pm
Re: MySQL: SELF JOIN, using dates as columns
I think that
is a bit faster. However, HAVING COALESCE produces a bit cleaner results (it gets rid of rows with all zeroes as well as NULLs).
I wonder if it would make sense to do both. The table will start to get quite large over time, adding about 500 rows a day on average.
Thanks
McMurphy
Code: Select all
peerReport.summary_date BETWEEN '2009-06-24' AND '2009-06-26'I wonder if it would make sense to do both. The table will start to get quite large over time, adding about 500 rows a day on average.
Thanks
McMurphy