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 ('Maciel Victoria Ortega 9383' , 'Maria Jose Tavarez 9374',
        'Francis Clarke 9370',
        'Madeline Altagracia Ruiz Garcia 9379',
        'Maribel Berroteran 9098',
        'Osiris Anton Encarnacion De Oleo 8992',
        'Jonathan Mejia 8965',
        'Rafael Ascension 9223')
        AND qtyonhand = 1
GROUP BY username
ORDER BY units DESC;