Finding Count of Occurrences & Time Gap between a Timeline in MySQL I have a Table, like the below, which has a timeline and values (like YES/NO) that are coming from different sensors. How do I do the following:

How many times there has a been a flip-flops (for YES to NO or NO to YES), in the below example it has happened 2 times.

1 , A0001, NO,  2017-06-14 02:03:26.0
2 , A0001, NO,  2017-06-14 02:03:26.0
3 , A0001, YES, 2017-06-14 02:03:26.0
4 , A0001, NO,  2017-06-14 02:03:26.0
5 , A0001, YES, 2017-06-14 02:03:26.0
6 , A0001, NO , 2017-06-14 02:03:26.0
Using Sql Server,

declare @t table(id int,col1 varchar(30), col2 varchar(30),col3 datetime)
insert into @t (id,col1,col2,col3) VALUES 
(1 , 'A0001' , 'NO',  '2017-06-14 02:03:26.0') 
,(2 , 'A0001' , 'NO', '2017-06-15 02:03:26.0') 
,(3 , 'A0001' , 'YES','2017-06-16 02:03:26.0') 
,(4 , 'A0001' , 'NO', '2017-06-17 02:03:26.0') 
,(5 , 'A0001' , 'YES','2017-06-18 02:03:26.0') 
,(6 , 'A0001',  'NO' ,'2017-06-19 02:03:26.0') 

;With CTE1 as
(
select *
,ROW_NUMBER()over(PARTITION by col1 order by col3)rn from @t
)

, cte
AS (
    SELECT
        rn
        ,col1
        ,col2
        ,col3
        ,0 changecount
    FROM cte1
    where rn=1

    UNION ALL

    SELECT 
        t.rn
        ,t.col1
        ,t.col2
        ,t.col3
        ,CASE 
            WHEN t.col2 &lt;&gt; t1.col2
                THEN changecount + 1
            ELSE changecount
            END
    FROM cte1 t
    INNER JOIN cte t1 ON t.col1 = t1.col1
        AND t.rn = t1.rn + 1
    )
SELECT *
FROM cte