MySQL: SELF JOIN, using dates as columns

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: MySQL: SELF JOIN, using dates as columns

Post by Weirdan »

VladSun wrote:
Weirdan wrote:
VladSun wrote:Well, in both "versions" the OP requirements are still not met (no list of "empty" rows)
well, that only requires a join to the table where the full list of those 'realms' is stored.
By "no list of "empty" rows" I meant that rows like:
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
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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL: SELF JOIN, using dates as columns

Post by VladSun »

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.
8O Example, please? I couldn't get the "by specifying the date range that is retrieved" part.

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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL: SELF JOIN, using dates as columns

Post by Eran »

I think weirdan meant something like

Code: Select all

WHERE peerReport.tab = 'term' AND peerReport.summary_date BETWEEN '2009-06-24' AND '2009-06-26'
Which would filter the rows that don't have a summary date in the range of the report
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL: SELF JOIN, using dates as columns

Post by VladSun »

pytrin wrote:I think weirdan meant something like

Code: Select all

WHERE peerReport.tab = 'term' AND peerReport.summary_date BETWEEN '2009-06-24' AND '2009-06-26'
Which would filter the rows that don't have a summary date in the range of the report
:oops:
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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: MySQL: SELF JOIN, using dates as columns

Post by Weirdan »

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.
8O Example, please? I couldn't get the "by specifying the date range that is retrieved" part.
Sure. Here it is:

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
 
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL: SELF JOIN, using dates as columns

Post by VladSun »

:oops: :oops:

:lol:

Yeah ... I finally got it!

I think I should turn on the air conditioning :) hahaha

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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL: SELF JOIN, using dates as columns

Post by VladSun »

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

Post by mcmurphy510 »

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
mcmurphy510
Forum Newbie
Posts: 6
Joined: Tue Jul 28, 2009 12:13 pm

Re: MySQL: SELF JOIN, using dates as columns

Post by mcmurphy510 »

I think that

Code: Select all

peerReport.summary_date BETWEEN '2009-06-24' AND '2009-06-26'
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
Post Reply