SELECT `g`.`id` g_id, `g`.`name` g_name, COUNT(`u`.`id`) g_count FROM `groups` g LEFT JOIN `users` u ON `g`.`id` = `u`.`user_group`
SELECT `id` AS g_id,
`name` ,
(
SELECT count( * )
FROM `users`
WHERE `users`.user_group = g_id
) AS user_in_group
FROM `groups`
SELECT `u`.`user_group`, `g`,`name`, COUNT(`u`.`user_group`)
FROM `users` AS `u`
JOIN `groups` AS `g` ON `g`.`id` = `u`.`user_group`
GROUP BY `u`.`user_group`