sql - count one column based on another column

You can try using row_number() function

select 
   caseid,
   subcase_id, 
   row_number() over(partition by caseid 
      order by 
      cast(SUBSTR(subcase_id, 1,INSTR(subcase_id, '-') -1) as number), 
      cast(SUBSTR(subcase_id, INSTR(subcase_id, '-') +1) as number)) as rn
from tablename

You may use count() over (partition by .. order by ..) clause as :

    with t(case_id,subcase_id) as
    (   
     select 1,'1-1' from dual union all
     select 1,'1-2' from dual union all
     select 1,'1-3' from dual union all
     select 1,'1-6' from dual union all
     select 2,'2-1' from dual union all
     select 2,'2-7' from dual
    )
     select t.*,
            count(*) over (partition by case_id order by subcase_id)              
            as result
       from t;

    CASE_ID     SUBCASE_ID   RESULT
    -------     ----------   ------ 
       1            1-1        1
       1            1-2        2
       1            1-3        3
       1            1-6        4
       2            2-1        1
       2            2-7        2

where subcase_id is changes frequently and distinct for all values while case_id changes rarely.

Rextester Demo

Here is a query which should behave as you want. We have to isolate the two numeric components of the subcase_id, and then cast them to integers, to avoid sorting this column as text.

SELECT
    case_id,
    subcase_id,
    ROW_NUMBER() OVER (PARTITION BY case_id
        ORDER BY TO_NUMBER(SUBSTR(subcase_id, 1, INSTR(subcase_id, '-') - 1)),
                 TO_NUMBER(SUBSTR(subcase_id, INSTR(subcase_id, '-') + 1))) rn
FROM yourTable
ORDER BY
    case_id,
    TO_NUMBER(SUBSTR(subcase_id, 1, INSTR(subcase_id, '-') - 1)),
    TO_NUMBER(SUBSTR(subcase_id, INSTR(subcase_id, '-') + 1));

Demo

It is not a good idea to treat the subcase_id column as both text and numbers. If you really have a long term need to sort on this column, then I suggest breaking out the two numeric components as separate number columns.