title: COUNT evaluate to zero if no matching records
body: <p>Take the following:</p>
  
  <pre><code>SELECT 
      Count(a.record_id) AS newrecruits
      ,a.studyrecord_id
  FROM 
      visits AS a 
  INNER JOIN 
  (
      SELECT 
          record_id
          , MAX(modtime) AS latest 
      FROM 
          visits 
      GROUP BY 
          record_id
  )  AS b 
  ON (a.record_id = b.record_id) AND (a.modtime = b.latest)
  WHERE (((a.visit_type_id)=1))
  GROUP BY a.studyrecord_id;
  </code></pre>
  
  <p>I want to amend the <code>COUNT</code> part to display a zero if there are no records since I assume <code>COUNT</code> will evaluate to <code>Null</code>.</p>
  
  <p>I have tried the following but still get no results:</p>
  
  <pre><code>IIF(ISNULL(COUNT(a.record_id)),0,COUNT(a.record_id)) AS newrecruits
  </code></pre>
  
  <p>Is this an issue because the join is on <code>record_id</code>? I tried changing the <code>INNER</code> to <code>LEFT</code> but also received no results.</p>
  
  <p><strong>Q</strong>
  How do I get the above to evaluate to zero if there are no records matching the criteria? </p>
  
  <p><strong>Edit:</strong></p>
  
  <p>To give a little detail to the reasoning.</p>
  
  <p>The studies table contains a field called 'original_recruits' based on activity before use of the database.</p>
  
  <p>The visits tables tracks new_recruits (Count of records for each study).</p>
  
  <p>I combine these in another query (original_recruits + new_recruits)- If there have been no new recruits I still need to display the original_recruits so if there are no records I need it to evalulate to zero instead of null so the final sum still works.</p>