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