MySQL Using Group By, Need to add fields where answer is reliant on another fields data I've asked a the same question before, but didn't get an answer and I believe I made the question too confusing to understand.

BEFORE CODE:

SELECT
inventory.WHSE AS `Warehouse`,
inventory.`CODE` AS `Item No.`,
inventory.INV_DESCRIPTION AS Description,
inventory.ONHAND AS `On Hand`,
pricing.BVRTLPRICE01 AS `SF Warehouse`,
pricing.BVRTLPRICE02 AS `LA Warehouse`,
pricing.BVRTLPRICE03 AS `HP Warehouse`,
pricing.BVRTLPRICE04 AS `FOB China/Indonesia`,
pricing.BVRTLPRICE05 AS ECOMM,
inventory.CUBE,
inventory.WEIGHT,
inventory.DIMENSION,
inventory.PROD AS `Status`
FROM
inventory
INNER JOIN pricing ON inventory.`CODE` = pricing.BVSPECPRICEPARTNO
WHERE
inventory.WHSE IN (00, 10, 20, 50, 70) AND
inventory.PROD IN ('A', 'A-70', 'B', 'C', 'S', 'KIT', 'SET')
GROUP BY
inventory.`CODE`,
inventory.WHSE ASC
ORDER BY
`Item No.` ASC,
inventory.WHSE ASC,
inventory.PROD


SQL before and after http://www.glgonline.com/pics/SQL.png

Can someone please help me figure this out? Thanks
Your question is not clear enough, but the following query would provide you with the results you specify as "AFTER :)"

SELECT  inventory.`CODE` AS `Item No.`,
        inventory.INV_DESCRIPTION AS Description,
        SUM(CASE WHEN inventory.WHSE = '00' THEN inventory.ONHAND ELSE 0 END) AS `On Hand (LA)`,
        SUM(CASE WHEN inventory.WHSE = '10' THEN inventory.ONHAND ELSE 0 END) AS `On Hand (SF)`,
        SUM(CASE WHEN inventory.WHSE = '20' THEN inventory.ONHAND ELSE 0 END) AS `On Hand (HP)`,
        SUM(CASE WHEN inventory.WHSE = '50' THEN inventory.ONHAND ELSE 0 END) AS `On Hand (CHINA)`,
        SUM(CASE WHEN inventory.WHSE = '70' THEN inventory.ONHAND ELSE 0 END) AS `On Hand (INDO)`
FROM inventory
WHERE inventory.WHSE IN ('00', '10', '20', '50', '70') AND 
      inventory.PROD IN ('A', 'A-70', 'B', 'C', 'S', 'KIT', 'SET')
GROUP BY inventory.`CODE`, inventory.INV_DESCRIPTION
ORDER BY 1