Results 1 to 2 of 2

Thread: Group and Sum

  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Question Unanswered: Group and Sum

    Hi guys,

    Hope someone can help me on this.

    My table looks like this
    NO AGENT_CODE USERID AMOUNT
    --- ---------- ------ ------
    1. AAA KONG 100
    2. AAA KONG-000 200
    3. AAA KONG-001 300
    4. BBB ALEX 100
    5. BBB ALEX-000 50
    6. CCC OH 300

    How do i write a unique SQL, which giving me as the result like this:-

    NO AGENT_CODE USERID AMOUNT
    --- ---------- ------ ------
    1. AAA KONG 600
    2. BBB ALEX 150
    3. CCC OH 300

    Thank you very much.

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Try this one:
    SELECT ROW_NUMBER () OVER(), AGENT_CODE, SUBSTR(USERID,1,4),SUM(AMOUNT) FROM table GROUP BY AGENT_CODE,SUBSTR(USERID,1,4)

Posting Permissions

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