MySQL query wrong COUNT

Question

I’m developing an app. I have to do a query where I see the rooms where I’m guest or admin. That's easy. The problem is that I want to get, at the same time, the number of players inside the same room.

SELECT rooms.id, rooms.name, rooms.buyin, rooms.administrator, rooms.creation_date,
       rooms.max_players, rooms.min_players, count(room_players.room)
FROM rooms, room_players
WHERE administrator = 1 and room_players.room = rooms.id 

I have a table with the rooms and the other (room_players) that is a match between users and rooms. If i delete the count(room_players.room) and and room_players.room = rooms.id I have the rooms that I’m admin. But I need the number of players inside the same room too.

Answer 1

Your query needs a group by. With the COUNT() in the select, it becomes an aggregation query that returns one row -- a summary of all the rows in the table.

SELECT r.id, r.name, r.buyin, r.administrator, r.creation_date,
       r.max_players, r.min_players, count(rp.room)
FROM rooms r join
     room_players rp
     ON rp.room = r.id 
WHERE administrator = 1 
GROUP BY r.id;

I also fixed the join syntax to be an explicit join and added table aliases to make the query more readable.

EDIT:

I suspect the query you want is something like:

SELECT r.id, r.name, r.buyin, r.administrator, r.creation_date,
       r.max_players, r.min_players, count(rp.room)
FROM rooms r join
     room_players rp
     ON rp.room = r.id 
WHERE administrator <> 1 
GROUP BY r.id
HAVING sum(user = 1) > 0;

Answer 2

How about subtracting the # of admin from the total users?