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