CODEWITHSUNDEEP
sqloracle-databaseoracle12c
simba
simba

Reputation: 277

sql - count one column based on another column

I have a dataset

case_id    subcase_id
1      | 1-1
1      | 1-2
1      | 1-3
1      | 1-6
2      | 2-1
2      | 2-7

I want the following output. The idea is to count the occurence of a subcase corresponding to a case.

case_id  subcase_id
1      | 1-1          | 1
1      | 1-2          | 2
1      | 1-3          | 3
1      | 1-6          | 4
2      | 2-1          | 1
2      | 2-7          | 2

Upvotes: 2

Views: 931

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

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.

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

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

Upvotes: 3

Related Questions