Show "0" in COUNT column when WHERE does not match?

Question

What I would like to do is retrieve all data from a table, and order them by the number of games the user played in a specific category. Is there any way I can use some sort of "COUNT WHERE" sql statement?

here's what i have so far. it will only return the user if they have played a game in the "fps" category, but I want it to show all users in descending order even if they have not played an fps game. please excuse my crappy tables

SELECT user_data.user, COUNT(played_games.game_cat) as 'count'
FROM user_data, played_games
WHERE user_data.user_id = played_games.user_id and played_games.game_cat = 'fps'
GROUP BY user_data.user_id
ORDER BY 'count' DESC;

user_data table

user_id | user

1       | jeff
2       | herb
3       | dug

played_games table

id | user_id | game | game_cat

1  |    2    | kill | fps
2  |    1    | shoot| fps
3  |    2    | COD  | fps
4  |    3    | dogs | cas

Answer 1

You need a LEFT OUTER JOIN to get the records even if a corresponding record does not exist in the other table.

SELECT user, coalesce(count(game_cat), 0) as count
FROM user_data LEFT OUTER JOIN played_games
ON user_data.user_id = played_games.user_id AND played_games.game_cat='fps'
GROUP BY user_data.user_id 
ORDER BY count desc;

Gives the following result on my screen

+------+-------+
| user | count |
+------+-------+
| herb |     2 |
| jeff |     1 |
| dug  |     0 |
+------+-------+