sanjay radadiya sanjay radadiya - 2 months ago 8
SQL Question

Running Count Reset On some Column Value in select query

I want to achieve running value but condition is reset on some specific column value as like

here is my select statement

with tbl(emp,salary,ord) as
(
select 'A',1000,1 from dual union all
select 'B',1000,2 from dual union all
select 'K',1000,3 from dual union all
select 'A',1000,4 from dual union all
select 'B',1000,5 from dual union all
select 'D',1000,6 from dual union all
select 'B',1000,7 from dual
)
select * from tbl


I want to reset count on emp B if the column value is B then count is reset to 0 and started again increment by 1

emp salary ord running_count
A 1000 1 0
B 1000 2 1
K 1000 3 0
A 1000 4 1
B 1000 5 2
D 1000 6 0
B 1000 7 1


here order column is ord

I want to achieve the whole thing by select statement not using the cursor.

Thanks in advanced.

Answer

You want to define groups were the counting takes place. Within a group, the solution is row_number().

You can define the group by doing a cumulative sum of B values. Because B ends the group, you want to count the number of B after each record.

This results in:

select t.*,
       row_number() over (partition by grp order by ord) - 1 as running_count
from (select t.*,
             sum(case when emp = 'B' then 1 else 0 end) over (order by ord desc) as grp
      from tbl t
     ) t;
Comments