SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE IndAusSeries(TeamA VARCHAR(3),TeamB VARCHAR(3),MatchDate Date ,WinsBy VARCHAR(3)); INSERT INTO IndAusSeries(TeamA,TeamB,MatchDate,WinsBy) VALUES ('Ind','Aus','01-10-2014','Ind'), ('Ind','Aus','01-15-2014','Ind'), ('Ind','Aus','01-19-2014','Ind'), ('Ind','Aus','01-23-2014','Aus'), ('Ind','Aus','01-27-2014','Ind'), ('Ind','Aus','01-31-2014','Ind'); SELECT TeamA,TeamB,MatchDate,WinsBy FROM IndAusSeries; with a as (select *, lag(winsby,1) over (order by matchdate) last from IndAusSeries), b as (select *, case when winsby = 'Ind' and last = 'Ind' then 1 when winsby = 'Ind' and last is null then 1 when winsby = 'Ind' and last = 'Aus' then 1 else 0 end as new from a) select teama, teamb, matchdate, winsby,new,last, case when winsby = 'Ind' and last is null then new + coalesce(lag(new) over (order by matchdate),0) when winsby = 'Ind' and last = 'Ind' then new + lag(new) over (order by matchdate) else 0 end as final from b;