1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Need help with query after "group by"

Discussion in 'MySQL' started by dazedays, Mar 31, 2016.

  1. #1
    I am extremely green regarding MySQL code and I am trying to figure out how to make my before look like my after. (See the included image.)
    Note: Some of the screen capture is omitted, only the relevant info is included...)
    [​IMG]

    I'm including the before code:

    SELECT
    inventory.WHSE AS `Warehouse`,
    inventory.`CODE` AS `Item No.`,
    inventory.INV_DESCRIPTION AS Description,
    inventory.ONHAND AS `On Hand (LA)`,
    inventory.ONHAND AS `On Hand (SF)`,
    inventory.ONHAND AS `On Hand (HP)`,
    inventory.ONHAND AS `On Hand (CHINA)`,
    inventory.ONHAND AS `On Hand (INDO)`,
    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

    I'm at a loss. I'm really good at finding answers online but I have no idea how to ask the question to get an answer. I'm hoping the above code and the image give enough info to the pros out there to move me in the right direction.

    SEMrush
     
    dazedays, Mar 31, 2016 IP
    SEMrush
  2. Zoidrums

    Zoidrums Greenhorn

    Messages:
    10
    Likes Received:
    0
    Best Answers:
    1
    Trophy Points:
    11
    #2
    I think, the biggest problem is your 'ASC' in the GROUP BY clause...
    If your sorting is always an ascending one, you don't need to specify 'ASC' everytime.
    If you are the owner of the database, you should avoid to use the word "CODE" for a column name...

    Did you try to sort with the number of the column you've got ? Like this :

    SELECT inventory.WHSE AS `Warehouse`,
    inventory.`CODE` AS `Item No.`,
    inventory.INV_DESCRIPTION AS Description,
    inventory.ONHAND AS `On Hand (LA)`,
    inventory.ONHAND AS `On Hand (SF)`,
    inventory.ONHAND AS `On Hand (HP)`,
    inventory.ONHAND AS `On Hand (CHINA)`,
    inventory.ONHAND AS `On Hand (INDO)`,
    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
    ORDER BY 2, 1, 17;
     
    Zoidrums, Feb 9, 2017 IP