Как вывести список пользователей из таблицы mysql и посчитать количество пользователей в каждой группе?

Question

[Georka]

Всем привет.
Кто знает как вывести список пользователей из таблицы mysql и посчитать количество пользователей в каждой группе? пожалуйста помогите разобраться, я новичок.

В БД имеется 2 таблицы: groups и users



Выполняю 2 запроса:
$groups = "SELECT * FROM `groups`";
$result1 = mysqli_query($db_connect, $groups);
$users = "SELECT COUNT(users.id) FROM users WHERE users.group IN (SELECT id FROM `groups`)";
$result2 = mysqli_query($db_connect, $users);

Пытаюсь сформировать таблицу для для раздела "Группы пользователей" в ПУ:

<table class="table">
    <tbody>
        <tr>
            <th width="70px" class="border-end text-center">id</th>
            <th class="border-end">name</th>
            <th width="70px" class="text-center">count</th>
        </tr>
        while($count = $result2->fetch_assoc()) {
            $arraydata = implode(',', $count);
        }
        while($row = $result1->fetch_assoc()) { 
            echo "
                <tr>
                    <td class=\"border-end text-center\">
                        " . $row["id"] . "
                    </td>
                    <td class=\"border-end\">
                        " . $row["name"] . "
                    </td>
                    <td class=\"border-end text-center\">
                        " . $arraydata . "
                    </td>
                </tr>
            ";
        }
    </tbody>
</table>

Прикрепляю скрин того что я хочу получить:

Comments

[Akina]

Не используйте служебные и зарезервированные слова в качестве имён объектов (полей, таблиц и пр.). Лучше всего - переименуйте их.

[Дмитрий]

Версия мускула будет не лишним

Answer 1

[Дмитрий Кузнецов]

По хорошему нужно вам составить 1 запрос, вместо 2ух.
Получаете список групп, потом через leftJoin делаете запрос к таблице пользователей и в селекте делаете сумму.

Примерно такой запрос:

SELECT groups.name, COUNT(users.id) as user_count FROM groups LEFT JOIN users ON groups.id = users.group GROUP BY groups.name

Запрос не тестировал, могу в чём-то ошибаться.

Comments

[Georka]

Огромное спасибо)
Кстати я до того как получил ваш ответ, задал этот вопрос GPT чату.
Он ответил:

SELECT g.id, g.name, COUNT(u.id) AS user_count FROM `groups` g LEFT JOIN users u ON g.id = u.group GROUP BY g.id

Answer 2

[Rsa97]

WITH `cte` AS (
  SELECT `group`, COUNT(*) AS `count`
    FROM `users`
    GROUP BY `group`
) SELECT `g`.`id`, `g`.`name`, IFNULL(`cte`.`count`, 0) AS `count`
  FROM `cte`
  RIGHT JOIN `groups` AS `g` ON `g`.`id` = `cte`.`group`