Average, array, multi query.help

[Dimhellfire]

Well i've got a big problem...i've got this table:

stats

StatsID primary autoinc
StatsPlayerID foreign
StatsSeasonID foreign
StatsMatchDate im using it as a 'foreign'
StatsVote
StatsYellow
StatsRed
StatsGoal

Now what i need is the average of each StatsVote based on StatsPlayerID.
Let's figure this:a tournament, where there are many teams and each team has many players.Each match a team plays, every player that...play...gets a mark, a vote.. i need the average for each player i thought i could use AVG() or COUNT but im not going anywhere...

Think that this result will be just a column of a table where other datas are obtained with this query:

$get_players = mysql_query("SELECT DISTINCT
a.PlayerName AS name, a.PlayerTeamID, a.PlayerPositionID,
b.PlayerPositionID, b.PlayerPositionName AS position,
c.OpponentID, c.OpponentName AS team,
a.PlayerPrice AS price
FROM tplls_players AS a
LEFT JOIN tplls_opponents AS c ON (a.PlayerTeamID = c.OpponentID)
LEFT JOIN tplls_playerpositions AS b ON (a.PlayerPositionID = b.PlayerPositionID)
WHERE a.PlayerPositionID = b.PlayerPositionID
AND a.PlayerTeamID = c.OpponentID
AND PlayerSeasonID = 2
ORDER by b.PlayerPositionID", $connection)
or die(mysql_error());

Anyone can help?

[aspdaddy]

Does this work? I havent tested it but it looks right .

By player

SELECT PlayerID, SUM(matchvote ) / COUNT(matchvote) as avgVote
GROUP BY PlayerID

Or by palyer and match

SELECT MatchID,PlayerID, SUM(matchvote ) / COUNT(matchvote) as avgVote
GROUP BY MatchID,PlayerID

[Dimhellfire]

Ok, u gave me a big help^^, by now i changed my first query into this:

$get_players = mysql_query("SELECT DISTINCT
a.PlayerName AS name, a.PlayerTeamID, a.PlayerPositionID,
b.PlayerPositionID, b.PlayerPositionName AS position,
c.OpponentID, c.OpponentName AS team,
a.PlayerPrice AS price,
d.StatsPlayerID, SUM(d.StatsVote) / COUNT(d.StatsVote) as mvoto
FROM tplls_players AS a
LEFT JOIN tplls_opponents AS c ON (a.PlayerTeamID = c.OpponentID)
LEFT JOIN tplls_playerpositions AS b ON (a.PlayerPositionID = b.PlayerPositionID)
LEFT JOIN tplls_stats AS d ON (a.PlayerID = d.StatsPlayerID)
WHERE a.PlayerPositionID = b.PlayerPositionID
AND a.PlayerTeamID = c.OpponentID
AND a.PlayerID = d.StatsPlayerID
AND PlayerSeasonID = 2
GROUP BY d.StatsPlayerID
ORDER by b.PlayerPositionID", $connection)
or die(mysql_error());

$i = 0;
while($data = mysql_fetch_array($get_players))

{ $player[$i] = $data['name'];
$playerid[$i] = $data['id'];
$mvoto[$i] = $data['mvoto'];

mysql_free_result($get_players);

echo "<table width=\"800\" bgcolor=\"$inside_c\" align=\"center\">
<tr>
<td width=\"90\" bgcolor=\"$bg1\" align=\"left\">{$data['name']}<br></td>
<td width=\"90\" bgcolor=\"$bg1\" align=\"center\">{$data['position']}<br></td>
<td width=\"70\" bgcolor=\"$bg1\" align=\"center\">Player Real Team</td>
<td width=\"70\" bgcolor=\"$bg1\" align=\"center\">{$data['price']}<br></td>
<td width=\"30\" bgcolor=\"$bg1\" align=\"center\">Partite<br></td>
<td width=\"30\" bgcolor=\"$bg2\" align=\"center\">($mvoto[$i])<br></td>
<td width=\"30\" bgcolor=\"$bg1\" align=\"center\">Goal fatti/subiti<br></td>
<td width=\"30\" bgcolor=\"$bg1\" align=\"center\">ammo<br></td>
<td width=\"30\" bgcolor=\"$bg1\" align=\"center\">esp<br></td>
<td width=\"330\" bgcolor=\"$bg1\" align=\"center\">vuoto<br></td>
</tr></table>
";
}

Now the problem is i get only 1 line, not a list of players...i believe the problem is in php...but im not able to solve it...anyone can help in that?