I 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.
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.
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.
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;
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...
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;
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.
But, thanks to some really helpful people at SitePoint's MySQL forum (r937 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.
SELECT week_totals.person_id,
week_totals.Name,
week_totals.week_num,
week_totals.week_start,
week_totals.week_end,
week_totals.AllStats
FROM (
SELECT person_id
, Name,
, WEEK(logdate) AS week_num,
, MIN(logdate) AS week_start,
, MAX(logdate) AS week_end,
, SUM(Stats) AS AllStats
FROM stat_table
WHERE group_id = 'XXX'
GROUP
BY person_id
, WEEK(logdate)
) AS week_totals
INNER
JOIN (
SELECT week_num
, MAX(AllStats) AS max_stats
FROM (
SELECT person_id
, WEEK(logdate) AS week_num
, SUM(Stats) as AllStats
FROM stat_table
WHERE group_id = 'XXX'
GROUP
BY person_id
, WEEK(logdate)
) AS week_totals_1
GROUP
BY week_num
) AS week_max GROUP BY person_id;
MySQL and I may just be on speaking terms again.
Special thanks to Blogger for maintaining such wonderful formatting in the queries posted above.
