tag:blogger.com,1999:blog-2820774362841086506.post-88779786087131671582008-07-01T15:04:00.006-06:002008-07-01T15:26:14.104-06:00I have a sort of love/hate relationship with MySQL. Although I probably shouldn't drag MySQL into this. SQL and I have a love/hate relationship. I love creating database driven sites since it allows for so many possibilities.<br /><br />But I'm no genius database guru, so when I have a complex query to try to put together our relationship usually is on the outs.<br /><br />I had been working on a solution to get a list of weekly stats for stats that get run daily. I managed to get a query working that allows me to find the weekly stats for a specific person for every week and sort them however I need.<br /><br /><blockquote>SELECT SUM(Stats) as AllStats, Name, week(logdate) AS week_num, MIN(logdate) AS week_start, MAX(logdate) AS week_end FROM stat_table WHERE person_id = 'XXXX' GROUP BY week(logdate) ORDER BY AllStats DESC;</blockquote><br /><br />However, I wanted to expand that to allow the query to pull the top people in a group and find out what week was their best week. This is where it started to break down. I thought I could switch things around a bit and use the following query...<br /><br /><blockquote>SELECT MAX(SUM(Stats)) as MaxStats, Name, person_id, week(logdate) AS week_num, MIN(logdate) AS week_start, MAX(logdate) AS week_end FROM stat_table WHERE group_id = 'XXX' GROUP BY week(logdate), person_id ORDER BY week_num DESC;</blockquote><br /><br />That was a no go. And the weird thing was if I took out the MAX() function at the beginning (and only select SUM(Stats)) I could run all stats for that group.<br /><br />But, thanks to some really helpful people at SitePoint's MySQL forum (<a href="http://www.r937.com">r937</a> in particular) I got a new, uber-complex subquerying query that seems to work great. I've posted it below for anyone else that is looking for a way to find the MAX() of a SUM() or wants to find weekly data when they have multiple records per week.<br /><br /><blockquote>SELECT week_totals.person_id, <br /> week_totals.Name,<br /> week_totals.week_num,<br /> week_totals.week_start,<br /> week_totals.week_end,<br /> week_totals.AllStats<br /> FROM (<br /> SELECT person_id<br /> , Name,<br /> , WEEK(logdate) AS week_num,<br /> , MIN(logdate) AS week_start,<br /> , MAX(logdate) AS week_end,<br /> , SUM(Stats) AS AllStats<br /> FROM stat_table <br /> WHERE group_id = 'XXX' <br /> GROUP <br /> BY person_id<br /> , WEEK(logdate)<br /> ) AS week_totals<br />INNER<br /> JOIN (<br /> SELECT week_num<br /> , MAX(AllStats) AS max_stats<br /> FROM (<br /> SELECT person_id<br /> , WEEK(logdate) AS week_num<br /> , SUM(Stats) as AllStats<br /> FROM stat_table <br /> WHERE group_id = 'XXX' <br /> GROUP <br /> BY person_id<br /> , WEEK(logdate)<br /> ) AS week_totals_1<br /> GROUP<br /> BY week_num<br /> ) AS week_max GROUP BY person_id;</blockquote><br /><br />MySQL and I may just be on speaking terms again.<br /><br />Special thanks to Blogger for maintaining such wonderful formatting in the queries posted above.Graydon Stonerhttp://www.blogger.com/profile/02430224727562166186noreply@blogger.com