Untitled

 SELECT 
    COUNT(DISTINCT serialnumber) AS units,
    COUNT(DISTINCT bin) AS bins,
    username,
    MAX(lastscandate) AS lastscandate,
    (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 ('Merly Liriano 9169' , 'Luisa Jon Felix 9234',
        'Francis Clarke 9370',
        'Osiris Anton Encarnacion De Oleo 8992',
        'Jonathan Mejia 8965',
        'Rafael Ascension 9223')
        AND qtyonhand = 1
GROUP BY username
ORDER BY units DESC;