roomId
roomName
activeUsers
totalUsers
Table:users
+---------+-------------------------+
| id(int) | active(bool/tiny int) |
+---------+-------------------------+
Table:rooms
+---------+----------------------+
| id(int) | name(varchar(255)) |
+---------+----------------------+
Table:room_members
+-------------+---------------+
| userId(int) | roomId(int) |
+-------------+---------------+
SELECT
rooms.id,
rooms.name,
(SELECT SUM(users.active) FROM users WHERE room_members.roomId = rooms.id) activeUsers,
(SELECT COUNT(users.id) FROM users WHERE room_members.roomId = rooms.id) totalUsers
FROM room_members
INNER JOIN rooms
ON room_members.roomId = rooms.id
INNER JOIN users
ON room_members.userId = users.id
WHERE room_members.userId = 1
You cound use a dinamic table with group by ( and a join between user and room_members)
SELECT
rooms.id,
rooms.name,
t1.activeUsers
FROM room_members
INNER JOIN rooms ON room_members.roomId = rooms.id
INNER JOIN users ON room_members.userId = users.id
INNER JOIN (
SELECT roomId, SUM(users.active) as activeUsers, COUNT(users.id) as totaleUsers
FROM users
INNER JOIN room_members on users.users.id = room_members.userId
group by roomId
) t1 on t1.roomID = rooms.id
WHERE room_members.userId = 1