Untitled

SELECT 
    COUNT(i.serialnumber) UnitTotal,
    COUNT(DISTINCT i.bin) CountofBins,
    CASE
        WHEN product LIKE '%-_Z%' AND bin = '' THEN 'Shoes'
        WHEN
            (i.location LIKE '7-D62%'
                OR i.location LIKE '7-D63%'
                OR i.location LIKE '7-E99%'
                OR i.location LIKE '7-E100%'
                OR i.location LIKE '7-E101%'
                OR i.location LIKE '7-E102%'
                OR i.location LIKE '7-E103%'
                OR i.location LIKE '7-E104%'
                OR i.location LIKE '7-E105%'
                OR i.location LIKE '7-UE105%')
        THEN
            'GOH'
        WHEN
            bin = ''
                AND (product LIKE '%-_Y%'
                OR product LIKE '%-_A%'
                OR product LIKE '%-UU%')
        THEN
            'BoxedInv'
        WHEN
            bin = ''
                AND (product NOT LIKE '%-_Y%'
                OR product NOT LIKE '%-_A%')
        THEN
            'Misc'
        WHEN bin != '' THEN 'Standard Bins'
        ELSE 'null'
    END AS InvType
FROM
    (SELECT 
        serialnumber, product, size, bin, location
    FROM
        inventoryitem
    WHERE
        (location LIKE '7-E%'
            OR location LIKE '7-D%'
            OR location LIKE '7-UD%')
            AND location not like '7-E30__-%'
            AND qtyonhand = 1) i
        JOIN
    product p ON p.code = i.product AND p.siteflag = 'F'
GROUP BY InvType