Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    Posts
    49

    Unanswered: Grouping by reference and a group within the reference

    Hi,

    I have a simple script which sums the values of items and then groups the output by the reference.

    Code:
    select reference,sum(Number) from mytable
    group by reference
    So with he example table below, I'd like to group by Reference, but create a sub group within the reference of (group anything where the first two characters of the reference begin 16

    i.e

    Reference, Number
    cat,2
    dog,1
    horse,3
    cat,6
    dog,3
    165,4
    162,3
    169,6

    My results would be

    cat,8
    dog,4
    horse,3
    16,13

    Is this possible?

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,

    simply construct an expression which returns the value expected in the result set and GROUP BY it instead of REFERENCE column.

    Based on your specification, it could be this:
    Code:
    CASE
      WHEN SUBSTR(reference,1,2) = '16' THEN '16'
      ELSE reference
    END
    If the requirements are different, just change this expression according to them.

  3. #3
    Join Date
    Oct 2010
    Posts
    49
    SELECT REFERENCE, CASE
    WHEN SUBSTR(REFERENCE,1,2) = '16' THEN '16'
    ELSE REFERENCE
    END,
    sum(AMT*(DECODE(drF,'D',-1,1)*decode(RFLAG,'Y',-1,1))) FROM MYTABLE
    WHERE cl3accountnum IN ('A1','A2','A3')
    GROUP BY REFERENCE
    HAVING sum(AMT*(DECODE(drF,'D',-1,1)*decode(RFLAG,'Y',-1,1))) <> 0
    ORDER BY 1


    This is what I now have, but how do i reference the case in my group?
    Ok, I have worked it out, thx for you're help.

    SELECT CASE
    WHEN SUBSTR(REFERENCE,1,2) = '16' THEN '16'
    ELSE REFERENCE
    END,
    sum(AMT*(DECODE(drF,'D',-1,1)*decode(RFLAG,'Y',-1,1))) FROM MYTABLE
    WHERE cl3accountnum IN ('A1','A2','A3')
    GROUP BY CASE
    WHEN SUBSTR(REFERENCE,1,2) = '16' THEN '16'
    ELSE REFERENCE
    END
    HAVING sum(AMT*(DECODE(drF,'D',-1,1)*decode(RFLAG,'Y',-1,1))) <> 0
    ORDER BY 1
    Last edited by Ziggadebo; 06-27-11 at 07:04.

Posting Permissions

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