By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `groups` (`group_id` INTEGER);
INSERT INTO `groups` (`group_id`) VALUES
('1'), ('2'), ('3'), ('4'), ('5');
Records: 5 Duplicates: 0 Warnings: 0
CREATE TABLE group_users (`id` INTEGER, `user_id` INTEGER, `group_id` INTEGER);
INSERT INTO group_users (`id`, `user_id`, `group_id`) VALUES
('1', '1', '1'), ('2', '1', '5'),
('3', '14', '2');
Records: 3 Duplicates: 0 Warnings: 0
SELECT DISTINCT MAX(group_id) AS group_id,
COUNT(*) OVER (PARTITION BY MAX(group_id)) AS members
FROM group_users
GROUP BY user_id;
| group_id | members |
|---|---|
| 2 | 1 |
| 5 | 1 |
WITH cte AS (
SELECT DISTINCT MAX(group_id) AS group_id,
COUNT(*) OVER (PARTITION BY MAX(group_id)) AS members
FROM group_users
GROUP BY user_id
)
SELECT g.group_id,
COALESCE(c.members, 0) AS members
FROM `groups` AS g LEFT JOIN cte AS c
ON c.group_id = g.group_id;
| group_id | members |
|---|---|
| 1 | 0 |
| 2 | 1 |
| 3 | 0 |
| 4 | 0 |
| 5 | 1 |