Untitled

SELECT 
    p.SiteFlag,
    COUNT(DISTINCT i.serialnumber) Units,
    i.Location,
    CONCAT('$',
            FORMAT(SUM(ROUND(p.wholesalecost)), 'c')) SumCost
FROM
    (SELECT 
        serialnumber, product, size, location, cost
    FROM
        inventoryitem
    WHERE
        location IN ('cant find:pending' , 'StagnantPP', 'Consolidation Discrepancy', 'Pending CC', 'Stocking Pending')
            AND qtyonhand = 1) i
        JOIN
    product p ON p.code = i.product
WHERE
    siteflag IN ('F' , 'R')
GROUP BY p.SIteflag , i.Location
ORDER BY p.SiteFlag , i.Location ASC