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 5016' , 'Johana Aracely Rodriguez 7605',
        'Jenny Yip 8201',
        'Elena Zavala Diaz 8375',
        'Christian Anthony Rivas 8884',
        'Beatriz Rivas 7606',
        'Flor Del Carmen Rivas 8749',
        'Jonathan Ryu 8324',
        'Coty Phanniphon 5546',
        'Laura Ayala 8041',
        'Josefina Quezada 6014',
        'Ruben Esparza 7701',
        'Salvador Benjaran Ortega 7780',
        'Yessica B Rivas Rivas 9269',
        'Daniela Joselin Valdivia Haro 9262')
        AND qtyonhand = 1
GROUP BY username
ORDER BY units DESC