Untitled

SELECT 
    o.receivedate,
    s.siteflag,
    o.cost,
    o.serialnumber,
    i.product,
    i.size,
    CASE WHEN o.product like 'FNEF%' THEN 'Renew'
    WHEN s.isFineJewelry = 1 THEN 'FineJewelry'
    ELSE 'N'
    END AS 'Priority',
    i.sellableqty,
    COUNT(DISTINCT o.serialnumber) units_on_hand,
    IF(SUM(o.location IN ('Editorial' , 'Editorial - Off Site',
            'Copywriting',
            'Editorial Styling',
            'Editorial - Stills',
            'Model Size',
            'Model Size - Steaming',
            'Photo Studio',
            'Styling',
            'Social',
            'Pulls',
            'Copywriting')) > 0,
        'Y',
        'N') AS pending_units,
    GROUP_CONCAT(DISTINCT o.location) locations
FROM
    (SELECT 
        product, option1 AS size, quantity, sellableqty
    FROM
        inventory) i
        JOIN
    inventoryitem o ON o.product = i.product
        AND i.size = o.size
        JOIN
    product s ON s.code = o.product
WHERE
    s.siteflag = 'F'
        AND o.location IN ('Editorial' , 'Editorial - Off Site',
        'Copywriting',
        'Editorial Styling',
        'Editorial - Stills',
        'Model Size',
        'Model Size - Steaming',
        'Photo Studio',
        'Styling',
        'Social',
        'Pulls',
        'Copywriting')
        AND qtyonhand = 1
GROUP BY i.product , i.size
HAVING sellableqty <= 0
ORDER BY i.product , i.size ASC