Rohit Dhiman Rohit Dhiman - 3 months ago 7
MySQL Question

Mysql Select query To fetch consequenting records with count and full info

Table:

id driver_id status

1 23 1
2 23 1
3 22 1
4 23 0
5 22 1
6 22 1
7 22 0
8 22 1
9 23 1


I want a select query to check driver_id with status 1, consequently three or more times, with total count and id

for above table result should be like:

id driver_id status total_count
3 22 1 3
5 22 1 3
6 22 1 3


because driver_id having status 1 three times without any other status like 0, not 23 because having status 1 at id 1 and id 2 and having status 0 at id 4 so total count < 3 .

This is my original table:

In this table i am checking for status 52

I have to fetch drivers who cancelled trip => 3 times in a row

In this table i am checking for status 52

Answer
/*
DROP TABLE T;
CREATE TABLE T(id INT,driver_id INT, status INT);
TRUNCATE TABLE T;
INSERT INTO T VALUES
(1,  23,        1),
(2,  23,        1),
(3,  22,        1),
(4,  23,        0),
(5,  22,        1),
(6,  22,        1),
(7,  22,        0),
(8,  22,        1),
(9,  23,        0);
*/
SELECT T.ID,T.DRIVER_ID,T.STATUS,S.RN Total_count FROM
(
SELECT U.BN,MAX(U.RN) RN FROM 
(
SELECT T.*,
         IF(CONCAT(T.DRIVER_ID,STATUS) = @PREV,@BN,@BN:=@BN+1) BN,
         IF(CONCAT(T.DRIVER_ID,STATUS) <> @PREV,@RN:=1,@RN:=@RN+1) RN,
         @PREV:=CONCAT(T.DRIVER_ID,STATUS) P
FROM     (SELECT @RN:=0,@BN:=0,@PREV:='') RN, T
ORDER BY DRIVER_ID,ID
) U
WHERE U.RN >= 3
GROUP BY U.BN
) S
JOIN
(SELECT T.*,
         IF(CONCAT(T.DRIVER_ID,STATUS) = @PREV1,@BN1,@BN1:=@BN1+1) BN,
         IF(CONCAT(T.DRIVER_ID,STATUS) <> @PREV1,@RN1:=1,@RN1:=@RN1+1) RN,
         @PREV1:=CONCAT(T.DRIVER_ID,STATUS) P
FROM (SELECT @RN1:=0,@BN1:=0,@PREV1:='') RN, T
ORDER BY DRIVER_ID,ID) T ON T.BN = S.BN

ORDER BY T.DRIVER_ID,T.ID

result

+------+-----------+--------+-------------+
| ID   | DRIVER_ID | STATUS | Total_count |
+------+-----------+--------+-------------+
|    3 |        22 |      1 |           3 |
|    5 |        22 |      1 |           3 |
|    6 |        22 |      1 |           3 |
+------+-----------+--------+-------------+