SELECT u.id, u.name, COUNT (o.id ) AS orders
FROM users u
  LEFT
  JOIN orders o ON o.user_id = u.id
WHERE u.active = 1
GROUP BY u.id, u.name;