title: MySQL query to count sequential occurrences of certain numbers
body: <p>Another "sequential occurrences" MySQL question, but I think it hasn't been asked before.</p>
  
  <p>I have a table that looks like this</p>
  
  <pre><code>+----+------+---------------------+------+-----------+--------------+
  | id | unit |       datetime      | idle | idlecount | boutduration |
  +----+------+---------------------+------+-----------+--------------+
  | 1  |  A   | 2009-12-04 08:10:05 |  139 |           |              |
  | 2  |  A   | 2009-12-04 08:20:05 |  107 |           |              |
  | 3  |  A   | 2009-12-04 08:30:05 |  0   |           |              |
  | 4  |  A   | 2009-12-04 08:40:05 |  144 |           |              |
  | 5  |  A   | 2009-12-04 08:50:05 |  0   |           |              |
  | 6  |  A   | 2009-12-04 09:00:05 |  0   |           |              |
  | 7  |  A   | 2009-12-04 09:10:05 |  58  |           |              |
  | 8  |  A   | 2009-12-04 09:20:05 |  0   |           |              |
  | 9  |  A   | 2009-12-04 09:30:05 |  0   |           |              |
  | 10 |  A   | 2009-12-04 09:40:05 |  0   |           |              |
  | 11 |  A   | 2009-12-04 09:50:05 |  0   |           |              |
  | 12 |  A   | 2009-12-04 10:00:05 |  107 |           |              |
  | 13 |  A   | 2009-12-04 10:10:05 |  0   |           |              |
  | 14 |  A   | 2009-12-04 10:20:05 |  144 |           |              |
  | etc...
  </code></pre>
  
  <p>I need to count the number of sequential occurrences of zeros in column <code>idle</code> and determine the duration of each sequence. Single occurrences are irrelevant. So the result should look like this</p>
  
  <pre><code>+----+------+---------------------+------+-----------+--------------+
  | id | unit |       datetime      | idle | idlecount | boutduration |
  +----+------+---------------------+------+-----------+--------------+
  | 1  |  A   | 2009-12-04 08:10:05 |  139 |           |              |
  | 2  |  A   | 2009-12-04 08:20:05 |  107 |           |              |
  | 3  |  A   | 2009-12-04 08:30:05 |  0   |           |              |
  | 4  |  A   | 2009-12-04 08:40:05 |  144 |           |              |
  | 5  |  A   | 2009-12-04 08:50:05 |  0   |     2     |   00:20:00   |
  | 6  |  A   | 2009-12-04 09:00:05 |  0   |           |              |
  | 7  |  A   | 2009-12-04 09:10:05 |  58  |           |              |
  | 8  |  A   | 2009-12-04 09:20:05 |  0   |     4     |   00:40:00   |
  | 9  |  A   | 2009-12-04 09:30:05 |  0   |           |              |
  | 10 |  A   | 2009-12-04 09:40:05 |  0   |           |              |
  | 11 |  A   | 2009-12-04 09:50:05 |  0   |           |              |
  | 12 |  A   | 2009-12-04 10:00:05 |  107 |           |              |
  | 13 |  A   | 2009-12-04 10:10:05 |  0   |           |              |
  | 14 |  A   | 2009-12-04 10:20:05 |  144 |           |              |
  | etc...
  </code></pre>
  
  <p>I assume this requires the use of <a href="http://dev.mysql.com/doc/refman/5.0/en/flow-control-statements.html" rel="nofollow">flow control statements</a> but I have never used that before and feel a tad intimidated by the MySQL documentation. Nevertheless I dream of a one-query-solution.</p>
  
  <p>Cheers, Tom</p>