Count total number of occurrence of two consecutive values in a table

Question

My table structure

+----+--------+
| id | status |
+----+--------+
|  1 |     10 |
|  2 |     21 |
|  3 |     22 |
|  4 |     29 |
|  5 |     30 |
|  6 |     32 |
|  7 |     33 |
|  8 |     21 |
|  9 |     22 |
| 10 |     23 |
| 11 |     21 |
| 12 |     22 |
| 13 |     23 |
+----+--------+

I want to count total number of times when status 22 comes just after status 21.

In this case the query should return 3.

sql fiddle

possible duplicate of [Mysql Counting the consecutive number rows that match](http://stackoverflow.com/questions/19541762/mysql-counting-the-consecutive-number-rows-that-match) — Dávid Szabó, Jan 01 '15 at 17:42
E.g. `SELECT COUNT(x.id) FROM testtable x JOIN testtable y ON y.id = x.id+1 AND y.status = x.status + 1 WHERE x.status = 21;` — Strawberry, Jan 01 '15 at 19:16

score 2 · Answer 1 · answered Jan 01 '15 at 17:45

2

Just use a Self Join with Conditional Aggregate

SELECT Sum(CASE WHEN a.status = 22 AND b.status = 21 THEN 1 END) As Stat_Count
FROM   testTable a
       LEFT OUTER JOIN testTable b
              ON a.id = b.id + 1

SQLFIDDLE DEMO

answered Jan 01 '15 at 17:45

Pரதீப்

91,748
19
131
172

2

what will happen when the id is not auto increment and has random values rather then in a series – ahhmarr Jan 01 '15 at 17:50
1

@ahhmarr Then on which basis you will say `22` is after `21`. There should be some order – Pரதீப் Jan 01 '15 at 17:51
the status has to be in consecutive rows ,values of the id doesn't matter ie id=1090 status=22 is just below id=1010 status=21 and there is now rows between them – ahhmarr Jan 01 '15 at 17:55
@ahhmarr whether `ID` will be in `order` – Pரதீப் Jan 01 '15 at 18:02

FuzzyTree · Answer 2 · 2015-01-01T20:34:12.237

If you can have gaps in your id's you can use a subquery to check whether the previous status of a 22 row is 21

select count(*)
from testtable a
where a.status = 22 and (select status from testtable b 
  where b.id < a.id order by id desc limit 1) = 21

http://sqlfiddle.com/#!2/9d567/2

Another way gets all id's of previous rows of rows with a status of 22 in derived table and then joins the ids to count how many have a status of 21

select count(*) from (
    select max(b.id) max_b_id
    from testtable a join testtable b on b.id < a.id
    where a.status = 22
    group by a.id
) t1 join testtable a on a.id = t1.max_b_id
where a.status = 21

score 0 · Answer 3 · answered Jan 01 '15 at 17:49

I have tried to solve it in php

$q="";
$q= mysqli_query("select *from testTable");
while($r=mysqli_fetch_assoc($q)){
  $rows[]=$r;
}
$success=0;
for ($i=0;$i<count($rows);$i++){

    if($rows[$i]['status']==21 and $rows[$i+1]['status']==22 ){
    $success+=1;
    }
}

echo $success;

Count total number of occurrence of two consecutive values in a table

3 Answers3

SQLFIDDLE DEMO