MySQL: count consecutive rows with the same value

Question

I have read a few similar questions on counting consecutive rows, but none of them gave me a clear answer. I hope someone could give me some help with my problem. I have the following table as an example.

create table medical
 (PatientID int,
  Date Date,
  TookTest int
 );

insert into medical(PatientID, Date, TookTest)
values
(1, '2014-01-01', 1),
(1, '2014-01-05', 1),
(1, '2014-01-10', 1),
(2, '2014-01-01', 1),
(2, '2014-01-10', 0),
(2, '2014-01-20', 1),
(3, '2014-01-01', 1),
(3, '2014-01-07', 1),
(3, '2014-01-12', 1),
(3, '2014-01-21', 1),
(4, '2014-01-03', 1),
(4, '2014-01-05', 1),
(4, '2014-01-22', 0),
(4, '2014-01-27', 1)

This table is used to find out which patient took a medical test on certain dates. The PatientID and date columns are pretty self-explanatory. The last column, TookTest is a binary indicator column where 1 indicates that a patient took a test and 0 otherwise. The patientID and date are sorted at the time of this table's creation. I would like to count the number of patients who took tests at least 3 times consecutively. In our example, PatientID 1 and 3 took 3 or more tests. So the answer is 2. Could anyone show me how to write a query in MySQL? Thanks for your help in advance!

Answer 1

SELECT 
    m_id 
FROM(
    SELECT
        m.PatientID AS m_id,
        m.Date AS m_date,
        m.TookTest,
        IF(m.TookTest = 1 AND @b = m.PatientID, @a := @a +1, @a := 0) AS new_count,
        @b := m.PatientID
    FROM medical m
JOIN (
    SELECT 
        @a := 0, 
        @b := 0
    ) AS t
  ) AS TEMP
WHERE new_count >= 2
GROUP BY m_id

this does the calculation for you.. only thing is it looks a little weird because the count starts at 0 instead of 1 so if its 3 consecutive the count will be 2. this does what you requested..... see the fiddle if you have questions http://sqlfiddle.com/#!2/22ba28/12