sql to summarize data

drop table t;

create table t(d date, v varchar2(1));

insert into t values ( sysdate+1, 'A');

insert into t values ( sysdate+2, 'A');

insert into t values ( sysdate+3, 'B');

insert into t values ( sysdate+4, 'A');

insert into t values ( sysdate+5, 'A');

insert into t values ( sysdate+6, 'A');

insert into t values ( sysdate+7, 'B');

insert into t values ( sysdate+8, 'B');

insert into t values ( sysdate+9, 'C');

insert into t values ( sysdate+10, 'C');

insert into t values ( sysdate+11, 'A');

insert into t values ( sysdate+12, 'A');

commit;

select distinct v, min(d) over(partition by c)d , count(c) over(PARTITION by c) cnt

from(

select d,v,sum(c) over(order by d) c

from (

select d,v,case when v <> nvl(lag(v,1) over(order by d),v) then 1 else 0 end c

from t))

order by 2;

---------------------------------------------

drop table tab_chart;

create table tab_chart(TYPE_OF_CHART varchar(100), DATE_OF_CHART date , TITLE varchar(100), ARTIST varchar(100));

insert into tab_chart values('Albums',to_date('27-02-83','dd-mm-yyyy'),'THRILLER','MICHAEL JACKSON');

insert into tab_chart values('Albums',to_date('06-03-83','dd-mm-yyyy'),'WAR','U2');

insert into tab_chart values('Albums',to_date('13-03-83','dd-mm-yyyy'),'THRILLER','MICHAEL JACKSON');

insert into tab_chart values('Albums',to_date('20-03-83','dd-mm-yyyy'),'THE HURTING','TEARS FOR FEARS');

insert into tab_chart values('Albums',to_date('27-03-83','dd-mm-yyyy'),'THE FINAL CUT','PINK FLOYD');

insert into tab_chart values('Albums',to_date('03-04-83','dd-mm-yyyy'),'THE FINAL CUT','PINK FLOYD');

insert into tab_chart values('Albums',to_date('10-04-83','dd-mm-yyyy'),'FASTER THAN THE SPEED OF NIGHT','BONNIE TYLER');

insert into tab_chart values('Albums',to_date('17-04-83','dd-mm-yyyy'),'LET''S DANCE','DAVID BOWIE');

insert into tab_chart values('Albums',to_date('24-04-83','dd-mm-yyyy'),'LET''S DANCE','DAVID BOWIE');

insert into tab_chart values('Albums',to_date('01-05-83','dd-mm-yyyy'),'LET''S DANCE','DAVID BOWIE');

insert into tab_chart values('Albums',to_date('08-05-83','dd-mm-yyyy'),'TRUE','SPANDAU BALLET');

insert into tab_chart values('Albums',to_date('15-05-83','dd-mm-yyyy'),'THRILLER','MICHAEL JACKSON');

insert into tab_chart values('Albums',to_date('22-05-83','dd-mm-yyyy'),'THRILLER','MICHAEL JACKSON');

insert into tab_chart values('Albums',to_date('29-05-83','dd-mm-yyyy'),'THRILLER','SOME OTHER ARTIST');

insert into tab_chart values('Albums',to_date('05-06-83','dd-mm-yyyy'),'THRILLER','MICHAEL JACKSON');

insert into tab_chart values('Albums',to_date('12-06-83','dd-mm-yyyy'),'THRILLER','MICHAEL JACKSON');

insert into tab_chart values('Albums',to_date('19-06-83','dd-mm-yyyy'),'SYNCHRONICITY','THE POLICE');

insert into tab_chart values('Albums',to_date('26-06-83','dd-mm-yyyy'),'SYNCHRONICITY','THE POLICE');

insert into tab_chart values('Albums',to_date('03-07-83','dd-mm-yyyy'),'FANTASTIC','WHAM!');

insert into tab_chart values('Singles',to_date('29-01-60','dd-mm-yyyy'),'STARRY EYED','MICHAEL HOLLIDAY');

insert into tab_chart values('Singles',to_date('05-02-60','dd-mm-yyyy'),'WHY','ANTHONY NEWLEY');

insert into tab_chart values('Singles',to_date('12-02-60','dd-mm-yyyy'),'WHY','ANTHONY NEWLEY');

insert into tab_chart values('Singles',to_date('19-02-60','dd-mm-yyyy'),'WHY','ANTHONY NEWLEY');

insert into tab_chart values('Singles',to_date('26-02-60','dd-mm-yyyy'),'WHY','ANTHONY NEWLEY');

insert into tab_chart values('Singles',to_date('04-03-60','dd-mm-yyyy'),'POOR ME','ADAM FAITH');

insert into tab_chart values('Singles',to_date('10-03-60','dd-mm-yyyy'),'POOR ME','ADAM FAITH');

insert into tab_chart values('Singles',to_date('17-03-60','dd-mm-yyyy'),'RUNNING BEAR','JOHNNY PRESTON');

insert into tab_chart values('Singles',to_date('24-03-60','dd-mm-yyyy'),'RUNNING BEAR','JOHNNY PRESTON');

insert into tab_chart values('Singles',to_date('31-03-60','dd-mm-yyyy'),'MY OLD MAN''S A DUSTMAN','LONNIE DONEGAN');

commit;

select distinct TYPE_OF_CHART, min(DATE_OF_CHART) over(partition by TYPE_OF_CHART,T) DATE_OF_CHART

, TITLE, ARTIST, count(*) over(partition by TYPE_OF_CHART,T) cnt

from (

select TYPE_OF_CHART, DATE_OF_CHART, TITLE, ARTIST, sum(t) over(partition by TYPE_OF_CHART order by DATE_OF_CHART) T

from (

select TYPE_OF_CHART, DATE_OF_CHART, TITLE, ARTIST, 

case when TITLE||ARTIST <>  nvl(lag(TITLE||ARTIST,1) 

over(partition by TYPE_OF_CHART order by DATE_OF_CHART),TITLE||ARTIST) then 1 else 0 end T

from tab_chart))

order by 1,2;