Sum of statistics for every player (goals/assists/cards etc.)

Question

I'm trying to create a page that displays statistics for every player. A sum of total goals, assists, yellow cards, red cards etc.

The details are stored in the matchdetails you can see below:

+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| player_id      | int(4)      | NO   |     | NULL    |       |
| match_id       | int(4)      | NO   |     | NULL    |       |
| season         | varchar(45) | NO   |     | NULL    |       |
| player_present | int(4)      | NO   |     | NULL    |       |
| player_away    | int(4)      | NO   |     | NULL    |       |
| goals          | int(4)      | NO   |     | NULL    |       |
| assists        | int(4)      | NO   |     | NULL    |       |
| yellowcards    | int(4)      | NO   |     | NULL    |       |
| redcards       | int(4)      | NO   |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+

+-----------+----------+-----------+----------------+-------------+-------+---------+-------------+----------+
| player_id | match_id | season    | player_present | player_away | goals | assists | yellowcards | redcards |
+-----------+----------+-----------+----------------+-------------+-------+---------+-------------+----------+
| 3         | 1        | 2011-2012 | 1              | 0           | 0     | 0       | 0           | 0        |
| 4         | 2        | 2011-2012 | 1              | 0           | 0     | 2       | 1           | 0        |
| 4         | 1        | 2011-2012 | 1              | 0           | 0     | 0       | 0           | 0        |
| 1         | 2        | 2011-2012 | 1              | 0           | 4     | 0       | 0           | 0        |
| 1         | 1        | 2011-2012 | 0              | 1           | 0     | 0       | 0           | 0        |
| 2         | 1        | 2011-2012 | 1              | 0           | 2     | 0       | 1           | 0        |
| 2         | 2        | 2011-2012 | 1              | 0           | 1     | 2       | 0           | 1        |
+-----------+----------+-----------+----------------+-------------+-------+---------+-------------+----------+

The thing I want to achieve is to see a row for every player in which his total matches played (player_present) , total matches away (player_away), total goals, total assists, total yellow cards and total red cards are being displayed.

The following code I'm using already displays the total goals scored, but it sums the amount of goals scored by all the players. I want it to show a sum of every detail for every player.

$sql = "SELECT SUM(goals) AS goals, 
player_id
FROM matchdetails
ORDER BY player_id ASC
"; 
$results = $db->query($sql); 

echo "<table border='0' id='stats' cellpadding='0' cellspacing ='0'>
foreach ($results as $row) {    
    echo "<td class=''>" , $row['player_id'] , ' ' , "</td>"; 
    echo "<td class=''>" , $row['goals'] , "</td>";
}
    echo "</tr>";
echo "</table>";

Answer 1

You need to use GROUP BY if you want to use SUM.

SELECT SUM(goals) AS goals, player_id
FROM matchdetails
GROUP BY player_id
ORDER BY player_id ASC

Also, not sure if copy/paste error or not, you are missing a closing quote in your code:

$sql = "SELECT SUM(goals) AS goals, 
player_id
FROM matchdetails
ORDER BY player_id ASC
"; 
$results = $db->query($sql); 

echo "<table border='0' id='stats' cellpadding='0' cellspacing ='0'>";
foreach ($results as $row) {    
    echo "<td class=''>" , $row['player_id'] , ' ' , "</td>"; 
    echo "<td class=''>" , $row['goals'] , "</td>";
}
    echo "</tr>";
echo "</table>";

Answer 2

Add a GROUP BY clause:

SELECT SUM(goals) AS goals, player_id
FROM matchdetails
GROUP BY player_id
ORDER BY player_id ASC