Untitled

 SELECT 
    COUNT(DISTINCT serialnumber) / 8 AS UPH,
    COUNT(DISTINCT serialnumber) AS units,
    COUNT(DISTINCT bin) AS bins,
    username,
    MIN(lastscandate) AS firstscan,
    MAX(lastscandate) AS lastscan,
    (SELECT 
            location
        FROM
            shard.inventoryitemupdate
        WHERE
            username = t1.username
                AND lastscandate >= TIMESTAMPADD(SECOND, 1, CURRENT_DATE)
        ORDER BY lastscandate DESC
        LIMIT 1) AS location
FROM
    shard.inventoryitemupdate t1
WHERE
    lastscandate >= TIMESTAMPADD(SECOND, 1, CURRENT_DATE)
        AND username IN ('Maciel Victoria Ortega 9383', 'Maria Jose Tavarez 9374',
        'Francis Clarke 9370',
        'Madeline Altagracia Ruiz Garcia 9379',
        'Maribel Berroteran 9098',
        'Osiris Anton Encarnacion De Oleo 8992',
        'Jonathan Mejia 8965',
        'Rafael Ascension 9223')
        AND qtyonhand = 1
GROUP BY username
ORDER BY units DESC;