Untitled

SELECT 
    i.product,
    i.size,
    i.sellableqty,
    i.quantity,
    COUNT(DISTINCT o.serialnumber) units_on_hand,
    GROUP_CONCAT(DISTINCT location) locations,
    IF(SUM(location IN ('receiving' , 'stocking pending',
            'receiving pending',
            'receiving defective',
            'Receiving Restore',
            'AA DTLA Office',
            'mending',
            'sa pending')) > 0,
        'Y',
        'N') AS pending_units
FROM
    (SELECT 
        product, option1 AS size, quantity, sellableqty
    FROM
        inventory
    WHERE
        product IN ('LOVF-WS2759' , 'LOVF-WD3882', 'LOVF-WD3881', 'LOVF-WS2727', 'LOVF-WP685', 'LOVF-WD3891', 'LOVF-WS2734', 'LOVF-WQ761', 'LOVF-WS2720', 'LOVF-WD3869', 'LOVF-WP697')) i
        JOIN
    inventoryitem o ON o.product = i.product
        AND i.size = o.size
WHERE
    qtyonhand = 1
GROUP BY o.product , o.size
ORDER BY o.product , o.size ASC