Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    Unanswered: Need to assign group number in sql

    The select stmt below is wrong. The desired result is displayed below:
    I want to consecutively number the resulting lines.

    Thank you for your help. I am sure the solution is quite simple , but it escapes me.

    SELECT DISTINCT SUBSTR(PAYROLL_REGION,1,1) "ALPHA" , COUNT( SUBSTR(PAYROLL_REGION,1,1)) "COUNT" , (select count(rownum) from dual) "GROUP #"
    FROM MYSCHEMA.PAYROLL
    WHERE MYSCHEMA.PAYROLL.PAYROLL_REGION=MYSCHEMA.PAYROLL.P AYROLL_REGION GROUP BY SUBSTR(PAYROLL_REGION,1,1)
    /
    *** Desired result, consecutively numbered lines (Labeled 'Group #' ) ***

    A COUNT GROUP #
    - --------- ---------
    A 135 1
    C 313 2
    E 172 3
    F 60 4
    G 286 5
    I 6 6
    M 62 7
    N 137 8
    O 12 9
    R 2 10
    S 488 11
    T 12 12
    U 10 13
    W 303 14

    14 rows selected.
    Last edited by wrwelden; 11-10-04 at 13:11.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    My, oh, my.
    This sure seems to me to be a classic homework problem.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    Exclamation Not a homework assignment - just asked for help

    Actually, I just need help. The group by clause in conjunction with 'rownum' is causing me trouble.

    If you can provide a solution to the problem stated above, I would greatly appreciate it. And I do realize that this is a textbook-like problem that I have probably seen before, but I don't have that textbook anymore.

    Thank you to anyone that can help.

    SELECT SUBSTR(PAYROLL_REGION,1,1) "ALPHA", COUNT(*) , ROWNUM
    FROM PAYROLL
    WHERE PAYROLL.PAYROLL_REGION=PAYROLL.PAYROLL_REGION
    group by SUBSTR(PAYROLL_REGION,1,1)
    Last edited by wrwelden; 11-10-04 at 17:39.

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Sorry you had to face Anacedents unique charm, if it is any consolation it is not you :-)

    It is one of the foibles of rownum or our/most peoples expectations of it, that it is best applied last, really, honestly last.

    Try this...

    Code:
    select alpha,
           t,
           rownum
    from   (              
           SELECT SUBSTR(PAYROLL_REGION,1,1) ALPHA, 
                  COUNT(*) t
           FROM   PAYROLL
           WHERE  PAYROLL.PAYROLL_REGION=PAYROLL.PAYROLL_REGION
           group by SUBSTR(PAYROLL_REGION,1,1)
           )
    Incidentally, I assume this is demo code, but your where clause is redundant.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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