title: SELECT with calculated column that is dependent upon a correlation
body: <p>I don't do a lot of SQL,and most of the time, I'm doing CRUD operations.  Occasionally I'll get something a bit more complicated.  So, this question may be a newbie question, but I'm ready.  I've just been trying to figure this out for hours, and it's been no use.</p>
  
  <p>So, Imagine the following table structure:</p>
  
  <pre><code>&gt; | ID | Col1 | Col2 | Col3 | .. | Col8 |
  </code></pre>
  
  <p>I want to select ID and a calculated column.  The calculated column has a range of 0 - 8 and it contains the number of matches to the query.  I also want to restrict the result set to only include rows that have a certain number of matches.</p>
  
  <p>So, from this sample data:</p>
  
  <pre><code>&gt; | 1 | 'a' | 'b' | 1 | 2 |  
  &gt; | 2 | 'b' | 'c' | 1 | 2 |  
  &gt; | 3 | 'b' | 'c' | 4 | 5 |  
  &gt; | 4 | 'x' | 'x' | 9 | 9 |  
  </code></pre>
  
  <p>I want to query on Col1 = 'a' OR Col2 = 'c' OR Col3 = 1 OR Col4 = 5 where the calculated result > 1 and have the result set look like:</p>
  
  <pre><code>&gt; | ID | Cal |
  &gt; | 1  |  2  |
  &gt; | 2  |  2  |
  &gt; | 3  |  2  |
  </code></pre>
  
  <p>I'm using T-SQL and SQL Server 2005, if it matters, and I can't change the DB Schema.</p>
  
  <p>I'd also prefer to keep it as one self-contained query and not have to create a stored procedure or temporary table.</p>