SELECT users.uid AS uid, COUNT(users_roles.rid) AS users_roles_rid
FROM {users} users
LEFT JOIN {users_roles} users_roles ON users.uid = users_roles.uid
GROUP BY uid

SELECT role.name, COUNT(users_roles.uid) AS users_per_role
FROM users_roles AS users_roles
INNER JOIN role AS role ON role.rid = users_roles.rid
GROUP BY role.name

function mymodule_views_pre_render(&$view) {
  switch($view->name) {
    case 'my-view-name':
      $results = db_query('
        SELECT role.name, COUNT(users_roles.uid) AS users_per_role
        FROM users_roles AS users_roles
        INNER JOIN role AS role ON role.rid = users_roles.rid
        GROUP BY role.name
      ')->fetchAll();

      foreach($results as $result) {
        $rows[] = array($result->name, $result->users_per_role);
      }

      $view->attachment_after = theme('table',
        array(
          'header' => array('Role', 'Number of Users'),
          'rows' => $rows,
        )
      );
      break;
  }
}