Untitled

SELECT 
    SUBSTRING_INDEX(SUBSTRING(location, 3, 11), '-', 1) bay,
    COUNT(DISTINCT bin) bins
FROM
    inventoryitem
WHERE
    location LIKE '7-D51%'
        AND qtyonhand = 1
GROUP BY bay
HAVING bins < 36;

SELECT 
    SUBSTRING(location, 1, 10) row,
    COUNT(DISTINCT serialnumber) serials
FROM
    inventoryitem
WHERE
    location LIKE '7-ULA32%'
        AND qtyonhand = 1
GROUP BY row