Untitled

SELECT 
    'Criterion 1' AS criteria, 
    firstname, 
    lastname, 
    manager, 
    siteid, 
    COUNT(*) AS record_count,
    GROUP_CONCAT(username SEPARATOR ', ') AS usernames
FROM 
    users
GROUP BY 
    firstname, lastname, manager, siteid
HAVING 
    COUNT(*) > 1

UNION ALL

SELECT 
    'Criterion 2' AS criteria, 
    firstname, 
    lastname, 
    NULL AS manager, 
    siteid, 
    COUNT(*) AS record_count,
    GROUP_CONCAT(username SEPARATOR ', ') AS usernames
FROM 
    users
GROUP BY 
    firstname, lastname, siteid
HAVING 
    COUNT(*) > 1

UNION ALL

SELECT 
    'Criterion 3' AS criteria, 
    lastname, 
    firstname, 
    NULL AS manager, 
    siteid, 
    COUNT(*) AS record_count,
    GROUP_CONCAT(username SEPARATOR ', ') AS usernames
FROM 
    users
GROUP BY 
    lastname, firstname, siteid
HAVING 
    COUNT(*) > 1

UNION ALL

SELECT 
    'Criterion 4' AS criteria, 
    NULL AS firstname, 
    NULL AS lastname, 
    NULL AS manager, 
    siteid, 
    COUNT(*) AS record_count,
    GROUP_CONCAT(username SEPARATOR ', ') AS usernames
FROM 
    users
GROUP BY 
    REPLACE(CONCAT(firstname, lastname, siteid), ' ', '')
HAVING 
    COUNT(*) > 1;