title: SQL - Window Count with reset
body: <p>i am using sql and trying to achieve the following results (running_count).</p>
  
  <p>I did tried out window function counting, but i do not know how to achieve the reseting of my counter.</p>
  
  <pre><code>select *,
       count(1) over 
  (partition by ID, DATE order by DATE rows unbounded preceding) -1 as "run_count"  from table
  </code></pre>
  
  <p>My (resulttable) should looks like this. The count should reset between days, but <strong>also</strong> when a B occurs in between A.</p>
  
  <pre><code>ID Date   Flag Running_Count
  1  10/10   A   1 
  **2  10/10   B   0** -&gt; reset
  **3  10/10   B   0** -&gt; reset
  4  10/10   A   1
  5  10/10   A   2
  6  10/10   A   3
  
  1  11/10   B   0
  2  11/10   B   0
  3  11/10   A   1
  4  11/10   A   2
  5  11/10   A   3
  6  11/10   A   4
  **7  11/10   B   0** -&gt; reset
  8  11/10   A   1
  </code></pre>