I have managed to hack an SQL Query together and it was working until I realised some members in the database have null names due to the plugin we are using removes non-ascii names. I am displaying these stats on my page and having someone with no name doesn't work too well. I know how to not display NULL when using the WHERE clause but i'm not too sure when no WHERE clause is used.
What I have so far -
SELECT player_stats.player_name, COUNT(player_kill.killer) FROM player_stats LEFT JOIN player_kill ON player_stats.player_id = player_kill.killer GROUP BY player_stats.player_name HAVING COUNT(player_kill.killer) > 1 ORDER BY COUNT(player_kill.killer) DESC;
WHERE clause goes between all the
JOIN clauses and
GROUP BY. If
WHERE player_name IS NOT NULL isn't working, the names are probably empty strings, not
NULL, so you need to check for that as well.
SELECT s.player_name, COUNT(*) AS count FROM player_stats AS s INNER JOIN player_kill AS k ON s.player_id = k.killer WHERE s.player_name IS NOT NULL AND s.player_name != '' GROUP BY s.player_name ORDER BY count DESC;
Also, if you don't want to get 0 counts, use
INNER JOIN rather than