Simple count query help?

[Mr Chris]

Hi Guys,

 

I have a table join that works fine which lists goalscorers in a football season:

 

SELECT th.team_id, 
       th.team_name AS team, 
       th.league, 
       re.report_id, 
       re.competition,
       s.player_id AS player, 
       s.report_id, 
       players.player_name, 
       players.position, SUM( s.goals ) AS total_goals
FROM player_stats s
INNER JOIN teams th ON 
  th.team_id = players.team_id
INNER JOIN 
  reports re ON re.report_id = s.report_id
LEFT OUTER JOIN players ON players.player_id = s.player_id
WHERE re.competition = '$x'
GROUP BY players.player_id
ORDER BY total_goals DESC

 

However on the count query;

 

players.position, SUM( s.goals ) AS total_goals

 

it also outputs all players in the database even if they have scored no/zero goals. Which I don't want.  I only want a list of players who have scored goals  So I want to add in the line something like:

 

WHERE total_goals !=''

 

Somewhere in my code to achieve this - Can anyone help?

 

Thanks

 

Chris

[Illusion]

GROUP BY players.player_id

HAVING total_goals>0

ORDER BY total_goals DESC