Counting frequencies

[kateland]

GAME_USERINV (stores a user's inventory of games they own)
gid (game id)
user_id

GAME_INV (master list of all games loaded)
gid (game id)
game_title

I'm trying to write a SQL statement that will plunk out the game that appears in the most users' inventories. The COUNT() function seems to be only to count items in a group...not the frequency. Pardon the funky code, this is phpbb not pure php.

I'm guessing an INNER JOIN is useful since it will ignore all the other games that DON'T exist in someone's inventory. yes??

[code]$sql = "SELECT gu.*, gi.gid, gi.game_title, COUNT(gu.gid) AS count_freq
FROM " . GAME_USERINV . " AS gu
INNER JOIN " . GAME_INV_TABLE . " AS gi ON gu.gid = gi.gid
GROUP BY gi.game_title";
[/code]

Yeah, this totally does not work. Help! :)

[Eugene]

$sql = "SELECT gu.*, gi.gid, gi.game_title, COUNT(DISTINCT gu.gid) AS count_freq
FROM " . GAME_USERINV . " AS gu
INNER JOIN " . GAME_INV_TABLE . " AS gi ON gu.gid = gi.gid
GROUP BY gi.game_title";

I hope that helps.