MargateSteve Posted February 26, 2011 Share Posted February 26, 2011 I am trying to compare a teams average attendance compared to the average across all teams. The query that show the average by team (column 'atte') is $att_table = mysql_query(" SELECT t.team_name as Tm , ROUND(AVG(g.attendance))atte , SUM(g.attendance) tot , MAX(g.attendance) max , MIN(g.attendance) min from teams t left join all_games g on t.team_id = g.home_team WHERE comp = '1' AND home_goals IS NOT NULL AND date BETWEEN '2010-07-01' AND '2011-06-31' GROUP BY t.team_id ORDER BY atte DESC "); A simple query to show the total average is $lgeav = mysql_query(" (AVG(g.attendance))atte2 from all_games g WHERE comp = '1' AND home_goals IS NOT NULL AND date BETWEEN '2010-07-01' AND '2011-06-31' "); I am hoping to put the second query into an array and use the two to show the teams whose own average attendance is higher than the total average attendance in bold, with something along the lines of while ($row_att_table = mysql_fetch_assoc($att_table)){ if ($row_att_table['atte'] > $lgeav['atte2']) echo '<tr style="font-weight:bold">'; else echo '<tr>'; I know that this can be done with views but in an attempt to broaden my knowledge (and also in case I ever have to do something similar on MySQL4) I wondered if there was a simple way to do this through PHP? Thanks in advance Steve Quote Link to comment Share on other sites More sharing options...
MargateSteve Posted February 27, 2011 Author Share Posted February 27, 2011 All sorted. I did not realise that if you put a subquery in there, it would be ignored by the grouping. $att_table = mysql_query("SELECT t.team_name as Tm, @rownum := @rownum+1 AS rank , ROUND(AVG(g.attendance))atte, SUM(g.attendance) tot, MAX(g.attendance) max, MIN(g.attendance) min, COUNT(g.attendance) gms, (SELECT AVG(g.attendance) from all_games g WHERE comp = '1' AND home_goals IS NOT NULL AND date BETWEEN '2010-07-01' AND '2011-06-31' ) lgeav from teams t left join all_games g on t.team_id = g.home_team WHERE comp = '1' AND home_goals IS NOT NULL AND date BETWEEN '2010-07-01' AND '2011-06-31' GROUP BY t.team_id ORDER BY atte DESC ") Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.