title: SQL equivalent of countif() across 2 columns
body: <p>I am aware that there is a question with the same title as this one, but I'm not sure it addresses my question.</p>
  
  <p>I have data that looks like this:</p>
  
  <pre><code>ID     Current    Nxt  
  1      1A          1B
  1      1B          1C
  2      2A          2B
  3      3A          3B
  3      3B
  </code></pre>
  
  <p>Using countif in Excel '=COUNTIF($B$2:$B$6,C2)'  I can get the data to look like this (formula is in occurred column):</p>
  
  <pre><code>ID     Current    Nxt  Occurred
  1      1A          1B  1 
  1      1B          1C  0 
  2      2A          2B  0
  3      3A          3B  1 
  3      3B              0 
  </code></pre>
  
  <p>Basically I'm just interested in indicating if the Nxt scheduled event has actually occurred (which is indicated if it exists in the data).</p>
  
  <p>However, I would like to replicate this in my SQL query which looks like this:</p>
  
  <pre><code>SELECT
  ID,
  Current,
  Nxt
  
  FROM
  Table
  </code></pre>
  
  <p>I think the answer could lie in doing something like: </p>
  
  <pre><code>sum(case when Current='1B' THEN 1 ELSE 0 END) over (partition by Current)
  </code></pre>
  
  <p>as suggested here: <a href="https://stackoverflow.com/questions/582637/sql-equivalent-of-countif">Sql Server equivalent of a COUNTIF aggregate function</a>.  The above works for one record, but the issue is that I need to make the criteria reference the contents of the Nxt column rather than have it hardcoded as the following doesn't work:</p>
  
  <pre><code>sum(case when Current=Nxt THEN 1 ELSE 0 END) over (partition by Current)
  </code></pre>
  
  <p>I guess this is because Current and Nxt never actually match on a row?</p>
  
  <p>For reference my database uses Oracle 11.</p>