[SOLVED] SUM help on a join

[Mr Chris]

Hi Guys,

 

Wondering if anyone can help me with this join query.  I use the below to get a players name for a football match from a players table.

 

Now this works Great:

 

SELECT player_stats.player_id, players.player_name, players.position, player_stats.goals
from player_stats 
INNER JOIN players ON players.player_id = player_stats.player_id
WHERE players.player_id=1

 

However, I now want to add up how many goals they have scored

 

 

Now I’ve tried doing this:

 

SELECT player_stats.player_id, players.player_name, players.position, SUM( player_stats.goals )
FROM player_stats
INNER JOIN players ON players.player_id = player_stats.player_id
WHERE players.player_id =1

 

So the total goals for this player would be 23

 

But get told I’m matching illegal groups.  Can anyone help?

 

Thanks

 

Chris

 

[DeadEvil]

Use LEFT JOIN instead of INNER JOIN..

 

$query = "SELECT player_stats.player_id, players.player_name, players.position, SUM( player_stats.goals ) "
	. "FROM player_stats "
	. "LEFT JOIN players ON players.player_id = player_stats.player_id "
	. "WHERE players.player_id = 1";
$result = mysql_fetch_assoc(mysql_query($query));
print $result['goals'];

[Mr Chris]

Thanks,

 

But using that join i'm still getting the same error:

 

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

 

SELECT player_stats.player_id, players.player_name, players.position, SUM( player_stats.goals )
FROM player_stats
LEFT JOIN players ON players.player_id = player_stats.player_id
WHERE players.player_id = 1

 

Also tried it in PHP with your code and got a mysql_fetch_assoc() error?

 

Any other ideas?

 

Thanks

 

Chris

[DeadEvil]

I forgot the alias

 

$query = "SELECT player_stats.player_id, players.player_name, players.position, SUM( player_stats.goals ) as total_goals "
	. "FROM player_stats "
	. "LEFT JOIN players ON players.player_id = player_stats.player_id "
	. "WHERE players.player_id = 1";
$result = mysql_fetch_assoc(mysql_query($query));
print $result['total_goals'];

 

Hope it work!

[fenway]

You also forgot the GROUP BY clause... it's only implicit if there is are only aggregate expressions in the select list.

[Oldiesmann]

Add GROUP BY players.player_id to the query (after the WHERE clause) and it will work.

[Mr Chris]

Thanks Guys