select user_groups.*, count(users.id) as user_count, count(orders.id) as order_count

from user_groups

left join users on user_groups.id=users.group_id
left join orders on users.id=orders.user_id

group by user_groups.id

id | user_count | order_count
1  | 5          | 67
2  | 1          | 1

select ug.*, count(distinct u.id) as user_count, count(distinct o.id) as order_count
from user_groups ug left join
     users u
     on ug.id = u.group_id left join
     orders o
     on u.id = o.user_id
group by ug.id