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.

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)