Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2010
    Posts
    6

    Unanswered: Combine records for the result

    Server:
    Database server = DB2/LINUXPPC 8.2.3

    Query:
    SELECT
    DISTINCT XORDERS_RELEASED.ORDERS_ID as ORDERS_ID,
    UPPER('WEB/' CONCAT XORDERS_RELEASED.CATENTDESC_NAME) as REFERENCE2,
    XORDERS_RELEASED.QUANTITY,
    CATENTSHIP.WEIGHT * XORDERS_RELEASED.QUANTITY as Weight,
    '1' as valueOption,
    '1' as voidIndicator,
    XORDERS_RELEASED.TOTALPRODUCT as TOTALPRODUCT,
    ORDRELEASE.STATUS as STATUS,
    XORDERS_RELEASED.TIMEPLACED as TIMEPLACED,
    XORDERS_RELEASED.SHIPTO_ADDRESS_FIELD1 as SHIPTO_ADDRESS_FIELD1,
    UPPER(XORDERS_RELEASED.SHIPTO_FIRSTNAME CONCAT ' ' CONCAT XORDERS_RELEASED.SHIPTO_LASTNAME) as fullName,
    XORDERS_RELEASED.SHIPTO_PHONE1 as SHIPTO_PHONE1,
    UPPER(XORDERS_RELEASED.SHIPTO_ADDRESS1) as SHIPTO_ADDRESS1,
    UPPER(XORDERS_RELEASED.SHIPTO_ADDRESS2) as SHIPTO_ADDRESS2,
    UPPER(XORDERS_RELEASED.SHIPTO_CITY) as SHIPTO_CITY,
    XORDERS_RELEASED.SHIPTO_STATE as SHIPTO_STATE,
    XORDERS_RELEASED.SHIPTO_ZIPCODE as SHIPTO_ZIPCODE,
    SHIPMODE.CODE as CODE,
    XORDERS_RELEASED.SHIPTO_EMAIL1 as Email
    FROM XORDERS_RELEASED AS XORDERS_RELEASED, ORDRELEASE as ORDRELEASE, SHIPMODE as SHIPMODE, CATENTSHIP as CATENTSHIP
    where ORDRELEASE.STATUS in ('PRN')
    and ORDRELEASE.ORDERS_ID = XORDERS_RELEASED.ORDERS_ID
    and ORDRELEASE.shipmode_id > 11051
    and ORDRELEASE.shipmode_id = SHIPMODE.shipmode_ID
    and CATENTSHIP.CATENTRY_ID = XORDERS_RELEASED.CATENTRY_ID
    order by XORDERS_RELEASED.TIMEPLACED;

    Question:
    I would like to combine the records with the same orders_id to one record as
    1. SUM (Weight) -- Get total weight for all items
    2. CONCAT XORDERS_RELEASED.CATENTDESC_NAME -- Append all item names together.

    How can I do this?
    Thanks a lot.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    1) use GROUP BY
    2) I do not understand what you want.

    Andy

  3. #3
    Join Date
    Apr 2010
    Posts
    6
    This is the result now:
    Orders_ID, Item Name, Weight
    558823 ,MARATHON ADULT TEE, 0.5
    558823 ,WOMEN TEE,0.5

    I want it to be this result from the query:
    558823 ,MARATHON ADULT TEE & WOMEN TEE,1

    The "group by orders_ID" is not working.
    Thanks.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You need to combine a recursive SQL and the group by. There are several examples of the recursion in this forum.

    Andy

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation not unique question

    Quote Originally Posted by javafun View Post
    This is the result now:
    Orders_ID, Item Name, Weight
    558823 ,MARATHON ADULT TEE, 0.5
    558823 ,WOMEN TEE,0.5

    I want it to be this result from the query:
    558823 ,MARATHON ADULT TEE & WOMEN TEE,1

    The "group by orders_ID" is not working.
    Thanks.
    You can find answer on your question here:

    http://www.dbforums.com/db2/1655605-...ncatanate.html

    Lenny

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •