SQLize Online / PHPize Online  /  SQLtest Online

create table profitrides( ID nvarchar(10) ,START_TIME time ,END_TIME time ,START_LOC nvarchar(1) ,END_LOC nvarchar(1)) insert into profitrides values ('dri_1','9:00:00','9:30:00', 'a','b') ,('dri_1','9:30:00','10:30:00', 'b','c') ,('dri_1','11:00:00','11:30:00', 'd','e') ,('dri_1','12:00:00','12:30:00', 'f','g') ,('dri_1','13:30:00','14:30:00', 'c','h') ,('dri_2','12:15:00','12:30:00', 'f','g') ,('dri_2','12:30:00','14:30:00', 'c','h'); with cte as( select * , case when start_time=lag(end_time,1)over(order by start_time) and start_loc=lag(end_loc,1)over(order by start_time) then 1 else 0 end as profit_ride from profitrides) select id,sum(profit_ride) as total_profit_rides from cte group by id