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 * ,lag(end_time,1)over(order by start_time) as nextridestarttime ,lag(end_loc,1)over(order by start_time) as nextridestartloc from profitrides) ,cte2 as( select id, count(id) as profitrides from cte where start_time=nextridestarttime and start_loc=nextridestartloc group by id) select id,count(id) as total_rides,coalesce(c.profitrides,0) from profitrides p left join cte2 c on p.id=c.id group by id