title: SQL query for counting changes of values in a column
body: <p>Given the following table structure:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>CAT</th>
  <th>NR</th>
  <th>DATE</th>
  <th>VERSION</th>
  <th>SOME_CODE</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>ABC</td>
  <td>123</td>
  <td>2009-02-19T00:00:00Z</td>
  <td>1</td>
  <td>OPP</td>
  </tr>
  <tr>
  <td>ABC</td>
  <td>456</td>
  <td>2009-03-18T00:00:00Z</td>
  <td>1</td>
  <td>ZUM</td>
  </tr>
  <tr>
  <td>ABC</td>
  <td>444</td>
  <td>2009-03-18T00:00:00Z</td>
  <td>1</td>
  <td>ZUM</td>
  </tr>
  <tr>
  <td>ABC</td>
  <td>456</td>
  <td>2009-04-18T00:00:00Z</td>
  <td>2</td>
  <td>XXX</td>
  </tr>
  <tr>
  <td>ABC</td>
  <td>456</td>
  <td>2009-04-18T00:00:00Z</td>
  <td>3</td>
  <td>XXX</td>
  </tr>
  <tr>
  <td>ABC</td>
  <td>456</td>
  <td>2009-04-18T00:00:00Z</td>
  <td>4</td>
  <td>UIO</td>
  </tr>
  <tr>
  <td>ABC</td>
  <td>456</td>
  <td>2009-05-18T00:00:00Z</td>
  <td>5</td>
  <td>RQA</td>
  </tr>
  <tr>
  <td>ABC</td>
  <td>444</td>
  <td>2009-03-18T00:00:00Z</td>
  <td>2</td>
  <td>MUZ</td>
  </tr>
  <tr>
  <td>DEF</td>
  <td>789</td>
  <td>2018-02-17T00:00:00Z</td>
  <td>1</td>
  <td>WER</td>
  </tr>
  <tr>
  <td>DEF</td>
  <td>637</td>
  <td>2018-02-16T00:00:00Z</td>
  <td>1</td>
  <td>FAW</td>
  </tr>
  <tr>
  <td>GHI</td>
  <td>248</td>
  <td>2018-02-17T00:00:00Z</td>
  <td>1</td>
  <td>QWE</td>
  </tr>
  <tr>
  <td>GHI</td>
  <td>248</td>
  <td>2019-02-17T00:00:00Z</td>
  <td>2</td>
  <td>PPP</td>
  </tr>
  <tr>
  <td>GHI</td>
  <td>357</td>
  <td>2020-02-16T00:00:00Z</td>
  <td>1</td>
  <td>FFF</td>
  </tr>
  <tr>
  <td>GHI</td>
  <td>357</td>
  <td>2020-02-16T00:00:00Z</td>
  <td>2</td>
  <td>GGG</td>
  </tr>
  <tr>
  <td>GHI</td>
  <td>357</td>
  <td>2020-02-16T00:00:00Z</td>
  <td>3</td>
  <td>LLL</td>
  </tr>
  <tr>
  <td>GHI</td>
  <td>357</td>
  <td>2020-02-16T00:00:00Z</td>
  <td>4</td>
  <td>LLL</td>
  </tr>
  <tr>
  <td>GHI</td>
  <td>357</td>
  <td>2020-08-16T00:00:00Z</td>
  <td>4</td>
  <td>FFF</td>
  </tr>
  <tr>
  <td>GHI</td>
  <td>357</td>
  <td>2020-10-16T00:00:00Z</td>
  <td>5</td>
  <td>ZZZ</td>
  </tr>
  <tr>
  <td>GHI</td>
  <td>420</td>
  <td>2020-02-16T00:00:00Z</td>
  <td>1</td>
  <td>QDS</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I need to find out how many effective changes took place in column SOME_CODE (sometimes values are the same and therefore they should not count) per month and category (CAT).</p>
  <p>So expected SQL query result should like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>CAT</th>
  <th>MONTH</th>
  <th>SOME_CODE_CHANGES</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>ABC</td>
  <td>APR 2009</td>
  <td>1</td>
  </tr>
  <tr>
  <td>ABC</td>
  <td>MAR 2009</td>
  <td>1</td>
  </tr>
  <tr>
  <td>GHI</td>
  <td>FEB 2020</td>
  <td>2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Does anybody have a hint how to start the query?</p>
  <p>Within a category (e.g. ABC) I want to count number of changes in column SOME_CODE. When a changed happened (e.g. for NR=456) say from VERSION 1 to 2 (&quot;ZUM&quot; -&gt; &quot;XXX&quot;) I wanna count it but with two conditions:</p>
  <ul>
  <li>must be a change within a month</li>
  <li>must be an effective change. Means the change from VERSION 3 to VERSION 4 is a valid change (&quot;XXX&quot; -&gt; &quot;UIO&quot;) while the change from VERSION 2 to VERSION 3 is nonesense (&quot;XXX&quot; -&gt; &quot;XXX&quot;) and should not be counted</li>
  </ul>
  <p>My approach:</p>
  <pre><code>select                                                                                                                                                                                                                                                                                      
  trunc(DATE, 'MON'),                                                                                                                                                                                                                                                             
  CAT,                                                                                                                                                                                                                                                                         
  sum(case when _MY_CONDITION_ then 1 else 0 end) counter                                                                                                                                                                                                                                     
  from table where date between TO_TIMESTAMP('01/06/2018 00:00:00.000000000', 'DD/MM/YYYY HH24:MI:SS.FF') and TO_TIMESTAMP('31/07/2018 00:00:00.000000000', 'DD/MM/YYYY HH24:MI:SS.FF')                                                                                           
  group by                                                                                                                                                                                                                                                                                    
      trunc(DATE, 'MON'),                                                                                                                                                                                                                                                               
      CAT                                                                                                                                                                                   
  order by                                                                                                                                                                                         
  trunc(DATE, 'MON')    
  </code></pre>