Jump to content

Query against an array


MargateSteve

Recommended Posts

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

Link to comment
Share on other sites

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
")

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.