title: SELECT CASE vs. CASE IN SQL
body: <p>I do not quite understand why those two different codesamples return a different value.</p>
  
  <p>somehow incorrect but working syntax, returns false results, e.g it returns <code>0</code> when the comparison is done over <strong>two equal</strong> values:</p>
  
  <pre><code>(SELECT CASE 
      WHEN 
         SUM(V.IsCompatible) OVER 
            (PARTITION BY ComputerName, UserID) = ApplicationCount 
      THEN 1 ELSE 0 END
  ) AS CompatibleUser
  </code></pre>
  
  <p>The one below returns the <strong>correct values</strong>, ie. <code>1</code> when there are <strong>two equal values</strong> compared.</p>
  
  <pre><code>(CASE 
      WHEN 
         SUM(V.IsCompatible) OVER 
           (PARTITION BY ComputerName, UserID) = ApplicationCount 
      THEN 1 ELSE 0 END
  ) AS CompatibleUser
  </code></pre>
  
  <p>or even simpler:</p>
  
  <pre><code>(SELECT CASE 
      WHEN 
         X = Y 
      THEN 1 ELSE 0 END
  ) AS Result
  </code></pre>
  
  <p><em>X = 22 AND Y = 22 => Result = 0</em></p>
  
  <pre><code>(CASE 
      WHEN 
         X = Y 
      THEN 1 ELSE 0 END
  ) AS Result
  </code></pre>
  
  <p><em>X = 22 AND Y = 22 => Result = 1</em></p>
  
  <p>I understand <strong>applying the correct syntax</strong> is important, and I am aware of the SELECT CASE syntax in T-SQL, but I do not understand how the <strong>first code sample is evaluated</strong> and delivering an unexpected result. </p>
  
  <p>update: full query in it's context</p>
  
  <pre><code>select userapplication.username,
     computerdetails.computername,
     sum(userapplication.iscompatible) 
         over (partition by computerdetails.computername, 
                            userapplication.userid) as compatiblecount,
     userapplication.applicationcount,
     ( case
         when sum(userapplication.iscompatible) 
             over (partition by 
             computerdetails.computername, 
             userapplication.userid) &lt;&gt; userapplication.applicationcount 
         then 0
         else 1
       end 
     ) as usercomputeriscompatible
  from   computerdetails
     right outer join usercomputer
       on computerdetails.computerid = usercomputer.computerid
     right outer join userapplication
       on usercomputer.gebruikerid = userapplication.userid 
  </code></pre>
  
  <p>so <code>userComputerIsCompatible</code> is the result in question here</p>