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.

Gordon Linoff · Accepted Answer · 2014-05-22 02:25:56Z

5

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;

edited May 22, 2014 at 2:25

answered May 22, 2014 at 2:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MiguelVeloso Over a year ago

It did work. Many thanks. I tried to join two queries, but didn't work and I don't know why. I liked your way, it's easier and simple to understand.

MiguelVeloso Over a year ago

But i have a problem with this, if i want to get the rooms that I'm not admin (only guest), it doesn't counts very well the number of players inside the room.

Gordon Linoff Over a year ago

@miguelpveloso . . . I don't know what admin is. If it is a column in room_players, then you can remove the where and after the group by: having sum(administrator = 1) > 0.

MiguelVeloso Over a year ago

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 AND rp.user = 1 GROUP BY r.id;

Is this possible? This doesn't return me the count of players inside the room.

FrozenFire · Accepted Answer · 2014-05-22 02:06:10Z

0

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

answered May 22, 2014 at 2:06

FrozenFire

67018 silver badges30 bronze badges

1 Comment

user1932079 Over a year ago

Wouldn't hurt to use GROUP BY or even, universe-forbid, naming fields the same in different tables.

Collectives™ on Stack Overflow

MySQL query wrong COUNT

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related