title: MySQL: How to count the number of deviations?
body: <p>I've got a table with two columns: timestamp and integer value (let's name these <code>date</code> and <code>value</code>). The second value gets swings between 0 and ~290 and then goes back to 0, so on the chart it looks like a waves (details are viewed <a href="https://i.stack.imgur.com/nL66R.png" rel="nofollow noreferrer"> on screenshot).</a></p>
  
  <p>How do I count the number of these drops to 0 in the set?</p>
  
  <p>Expected result is a number of drops from high point to zero (as you can see on the picture). MySQL version is 5.5.37-0+wheezy1.</p>
  
  <p>Data sample:</p>
  
  <pre><code>data                                  value
  2017-10-10 00:00:00                   270
  2017-10-10 00:00:01                   270
  2017-10-10 00:00:02                   270
  2017-10-10 00:00:03                   265
  2017-10-10 00:00:04                   263
  2017-10-10 00:00:05                   184
  2017-10-10 00:00:06                   87
  2017-10-10 00:00:07                   23
  2017-10-10 00:00:08                   0
  2017-10-10 00:00:09                   0
  2017-10-10 00:00:10                   0
  2017-10-10 00:00:11                   0
  2017-10-10 00:00:12                   24
  2017-10-10 00:00:13                   87
  2017-10-10 00:00:14                   189
  2017-10-10 00:00:15                   241
  2017-10-10 00:00:16                   267
  2017-10-10 00:00:17                   267
  2017-10-10 00:00:18                   265
  2017-10-10 00:00:19                   266
  </code></pre>