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
        ORDER BY lastscandate DESC
        LIMIT 1) AS location
FROM
    shard.inventoryitemupdate t1
WHERE
        DATE(lastscandate) >= (CURDATE() - interval 7 day)
        AND username = 'Joshua Holdren'
        AND qtyonhand = 1
GROUP BY username
ORDER BY units DESC