Untitled

SELECT 
    o.lastscandate,
    s.siteflag,
    o.cost,
    o.serialnumber,
    i.product,
    i.size,
    s.isfinejewelry,
    CASE
        WHEN s.isFineJewelry = 1 THEN 'Y,FineJewelry'
        WHEN o.product LIKE 'FNEF%' THEN 'Y,Renew'
        WHEN o.cost > 1000 THEN 'Y,HighCost'
        ELSE 'N'
    END AS 'Priority',
    i.sellableqty,
    COUNT(DISTINCT o.serialnumber) units_on_hand,
    IF(SUM(o.location LIKE 'STUD%') > 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 LIKE 'STUD%'
        AND o.location != 'STUD-Sellable Photo Studio'
        AND qtyonhand = 1
GROUP BY i.product , i.size
HAVING sellableqty <= 0
ORDER BY Priority DESC