title: Mysql gaps and islands issue?
body: <p>Would like to count number of rows between occurrence where total number of score is above 60, and count number of rows where this occurrence doesn't happened.
  For example 5th and 6th game will end up with more then 60, so will count 5 rows with not 60 and 2 rows with 60.</p>
  
  <p>I would like something like this:</p>
  
  <pre><code>type                 count
  no-60-gap            5
  yes-60-island        1
  no-60-gap            3
  yes-60-island        2
  </code></pre>
  
  <p>I have simple database of basketball games. Rough example below</p>
  
  <pre><code>id, home,    score_home,  away,     score_away, round
  1,  team_1,  33,          team_2,   23,         1 
  2,  team_4,  31,          team_1,   33,         1 
  3,  team_2,  36,          team_5,   53,         2 
  4,  team_5,  35,          team_1,   63,         2 
  5,  team_7,  31,          team_8,   53,         3 
  6,  team_2,  30,          team_1,   43,         3 
  7,  team_1,  39,          team_3,   13,         4 
  </code></pre>
  
  <p>Was google-ing out this issues and I have end up trying to solve my problem with gaps and islands.</p>
  
  <p>This is my solution but it not working. Idea was just to reset gap/island to 0 when it doesn't happened. 
  I am kind a new in this.</p>
  
  <pre><code>SET @gap :=0; SET @island :=0;
  SELECT 
  @gap, 
  @island
  FROM (
  SELECT 
  CASE 
      WHEN (score_home + score_away) &gt;= 60 THEN @island:=@island+1 
      WHEN (score_home + score_away) &gt;= 60 THEN @gap:=0 
  END,
  CASE 
      WHEN (score_home + score_away) &lt; 60 THEN @island:=0 
      WHEN (score_home + score_away) &lt; 60 THEN @gap:=@gap+1
  END
  FROM basket
  
  ) AS games
  </code></pre>
  
  <p>Appreciate any help</p>