title: SQL Server subquery result output
body: <p>I want to create a result that lists all clients added to database yesterday and then a separate column for the same set of clients but only those without an email address, so we can see which CSR rep isn't adding email addresses, i am having trouble writing a subquery for counts.  Below is my code, its using two tables CLIENT and CONTACT they join on ACCT_NO field </p>
  
  <p>I am using SQL Server Management Studio</p>
  
  <pre><code>select 
      count(distinct c.acct_no) as Total, ADDED_BY 
  from 
      client c
  inner join 
      (select 
           count(distinct ct.acct_no) acct_NO 
       from 
           contact CT 
       inner join 
           client on client.acct_no = ct.ACCT_NO 
                  and ct.EMAILADDR1 = ''
                  and ct.[PRIMARY] = 'A' 
       group by 
           ct.ACCT_NO) x on c.ACCT_NO = x.acct_NO 
  where 
      c.DATE_ADDED = CONVERT(varchar(10), GETDATE()-10, 121)
  group by 
      c.ADDED_BY
  </code></pre>