Group users (total users, admin users, control operators, guards) using Eloquent queries

Question

I have a query which counts all users , the total number of users with role "admin" , the total number of users with role "control_operator" and Total number of users with role "guard" .

The query is working, but there is a glitch/bug that I can't figure out.

There are actually 2 users in the database, one with the super_admin and admin roles and the other with the control_operator. The front-end display is like this:

Why are there 3 users when there are only 2 users?

This is my query

// Retrieve the counts of admins, users, control operators, and security guards
        $countData = User::selectRaw('
        SUM(CASE WHEN roles.name = "admin" THEN 1 ELSE 0 END) as totalAdmins,
        COUNT(*) as totalUsers,
        SUM(CASE WHEN roles.name = "control_operator" THEN 1 ELSE 0 END) as totalControl,
        SUM(CASE WHEN roles.name = "security_guard" THEN 1 ELSE 0 END) as totalGuards
    ')->join('model_has_roles', 'users.id', '=', 'model_has_roles.model_id')
      ->join('roles', 'model_has_roles.role_id', '=', 'roles.id')
      ->first();

Answer 1

$countData = User::selectRaw('
    COUNT(DISTINCT users.id) as totalUsers,
    SUM(CASE WHEN roles.name = "admin" THEN 1 ELSE 0 END) as totalAdmins,
    SUM(CASE WHEN roles.name = "control_operator" THEN 1 ELSE 0 END) as totalControl,
    SUM(CASE WHEN roles.name = "security_guard" THEN 1 ELSE 0 END) as totalGuards
')
    ->join('model_has_roles', 'users.id', '=', 'model_has_roles.model_id')
    ->join('roles', 'model_has_roles.role_id', '=', 'roles.id')
    ->first();