René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Adjacent sum with SQL

set feedback off

create table sum_ (col1 number, col2 char(1));

insert into sum_ values ( 1,'A');
insert into sum_ values ( 2,'A');
insert into sum_ values ( 3,'B');
insert into sum_ values ( 4,'A');
insert into sum_ values ( 5,'C');
insert into sum_ values ( 6,'C');
insert into sum_ values ( 7,'A');
insert into sum_ values ( 8,'A');
insert into sum_ values ( 9,'A');
insert into sum_ values (10,'A');
insert into sum_ values (11,'C');
insert into sum_ values (12,'C');

select
  row_last_different - lag(row_last_different,1,0) over (order by col1) cnt,
  col2
from (
  select
    col1,
    row_last_different,
    col2 from (
      select
      col1,
      decode(lead (col2,1) over(order by col1),col2,null,rank() over(order by col1)) row_last_different,
      col2
    from 
      sum_
    )
    where row_last_different is not null
);

drop table sum_;