Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009
    Posts
    24

    Unanswered: GROUP BY substring help

    I'm trying to SUM and group data by the first 3 characters of a string(DIC code). However I can't get the same DIC codes to combine into one and total the Qty's.

    How can I modify the code below to get the results I want? I tried removing data1 from the group by clause but that fails.

    Code:
    SELECT substring(data1,1,3) AS DIC,
           substring(data1,8,13) AS NSN,
           SUM(substring(data1,25,5)*1) as Qty
    FROM v_intfce
    WHERE substring(data1,8,13) LIKE '8455010884943%'
    GROUP BY substring(data1,1,3), data1
    ORDER BY substring(data1,1,3) DESC
    Result:
    DIC - NSN - Qty
    DUS 8455010884943 100
    DUS 8455010884943 200
    DUS 8455010884943 1
    DUS 8455010884943 1
    D6B 8455010884943 18
    D4S 8455010884943 70
    D4S 8455010884943 14
    D4S 8455010884943 70
    AR0 8455010884943 30
    AR0 8455010884943 12

    Wanted results:
    DIC - NSN - Qty
    DUS 8455010884943 302
    D6B 8455010884943 18
    D4S 8455010884943 154
    AR0 8455010884943 42

    Thanks for any help!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It is because you are grouping by the entire string, as well as the substring
    Code:
    GROUP BY substring(data1,1,3), data1

    You should use :
    Code:
    GROUP BY substring(data1,1,3), substring(data1,8,13)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Oct 2009
    Posts
    24
    Thanks blindman. I thought I had tried that combination and it complained about data1 not being in the group by. I guess not.

Posting Permissions

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