title: SQL query to get count of rows with more than one row in another table
body: <p>Within my application, I have a set of filters that can be applied when listing resources, which builds up a query by adding WHERE clauses, etc. before executing the query.  This is using SQL Server 2008.</p>
  
  <p>I have two pertinent tables, one which contains some static data about the resource, and another which can contain arbitrary/optional fields pertaining to that resource.</p>
  
  <p>First table is something like this (table names &amp; fields changed):</p>
  
  <pre><code>CREATE TABLE Resources (
      ResID       varbinary(28),
  
      ... extra stuff omitted
  
      type        integer );
  </code></pre>
  
  <p>The second table just has name/value pairs and the corresponding resource ID</p>
  
  <pre><code>CREATE TABLE ResourceFields (
      ResID       varbinary(28) NOT NULL,
  
      Name        nvarchar(255) NOT NULL,
      Value       nvarchar(1024) NOT NULL);
  </code></pre>
  
  <p>So, for this example, there could be multiple rows in 'ResourceFields' with name = 'ContactName' for the same ResID.</p>
  
  <p><strong>What I want to do is get a count of the number of rows in the 'Resources' table that have more than one 'ContactName' listed in 'ResourceFields' with 'type' equal to some value.</strong></p>
  
  <p>I came up with this (don't laugh -- I know just enough SQL to cause problems)</p>
  
  <pre><code>SELECT count(r.ResID) 
      FROM Resources as r 
          INNER JOIN ResourceFields AS rf 
              ON rf.ResID = r.ResID 
                  AND rf.name = 'ContactName' 
      WHERE r.type = 1 
      GROUP BY rf.ResID 
      HAVING COUNT(rf.Value) &gt; 1;
  </code></pre>
  
  <p>but instead of returning the count of the number of rows (43 in my test set) in 'Resources', I get all the COUNT(rf.Value) values returned (that is, 43 individual counts).</p>
  
  <p>What am I doing wrong?</p>