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 ('Joshua Holdren' , 'Johana Aracely Rodriguez',
        'Jenny Yip',
        'Elena Zavala Diaz',
        'Christian Anthony Rivas',
        'Flor Del Carmen Rivas',
        'Jonathan Ryu',
        'Coty Phanniphon',
        'Laura Ayala',
        'Josefina Quezada',
        'Ruben Esparza',
        'Salvador Benjaran Ortega')
        AND qtyonhand = 1
GROUP BY username
ORDER BY units DESC