CODEWITHSUNDEEP
mysql
php_nub_qq
php_nub_qq

Reputation: 16055

Mysql invalid use of group function with Count?

I am building a game thing and I'm currently working on the matching players mechanism but I get this error I cant figure. I have 2 tables games which holds information about different instances of the game and players which holds players in each game. Now when a new player joins the matchmaking queue I want to select all games that haven't currently started and add them ( the player ) to the respective game. How I recognize a not-started game is by the number of players in it. If a game is meant for 2 players then any game with 1 player hasn't started yet. And here's my query

SELECT * // * for saving time in testing
FROM games d LEFT JOIN players dp ON d.id = dp.gameId 
WHERE d.players > COUNT(dp.playerId) LIMIT 1

But I get this annoying error

1111 - Invalid use of group function

I tried it like this

SELECT *, COUNT(dp.playerId) AS cPlayers 
FROM games d LEFT JOIN players dp ON d.id = dp.gameId 
WHERE d.players > cPlayers LIMIT 1

but then I get

1054 - Unknown column 'cPlayers' in 'where clause'

Upvotes: 0

Views: 885

Answers (2)

CodeBird
CodeBird

Reputation: 3858

I guess this should work.

SELECT * 
FROM games d LEFT JOIN players dp ON d.id = dp.gameId
GROUP BY d.id
HAVING d.players > COUNT(dp.playerId) LIMIT 1

Upvotes: 2

Ameer Tamboli
Ameer Tamboli

Reputation: 1298

You should use GROUP BY on fields to use aggregation functions like COUNT(dp.playerId).

Try this

SELECT COUNT(dp.playerId) AS cPlayers 
FROM games d LEFT JOIN players dp ON d.id = dp.gameId group by dp.playerId  
WHERE d.players > cPlayers LIMIT 1

Upvotes: -1

Related Questions