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 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 ('Johana Aracely Rodriguez 7605' , 'Jenny Yip 8201',
        'Elena Zavala Diaz 8375',
        'Amber Rincon 8430',
        'Christian Anthony Rivas 8884',
        'Laura Ayala 8041',
        'Yesenia Salgado 7089',
        'Josefina Quezada 6014',
        'Ruben Esparza 7701',
        'Carlos Daniel Gil Pimentel 8936',
        'Jonathan Joaquin Vargas Negrete 9515',
        'Christian Garcia 7032',
        'Yessica B Rivas Rivas 9269',
        'Ivonne Trancoso 5243',
        'Yosvany Parra 8427',
        'Miriam Garcia 8185',
        'Daniela Joselin Valdivia Haro 9262')
        AND qtyonhand = 1
GROUP BY username
ORDER BY units DESC