title: How to use sub query in group by clause sql server 2005
body: <p>my table data as follows</p>
  
  <pre><code>FinishDate                  SpecialistName  jobstate
  -----------------------     --------------- ---------
  2012-10-01 00:00:00.000     Josh            FINISHED
  2012-10-01 00:00:00.000     Josh            FINISHED
  2012-10-01 00:00:00.000     Sam             FINISHED
  2012-10-01 00:00:00.000     Robin           FINISHED
  2012-10-01 00:00:00.000     Robin           FINISHED
  2012-10-01 00:00:00.000     Joy             FINISHED
  2012-10-01 00:00:00.000     Joy             INCOMMING
  2012-10-02 00:00:00.000     Joy             FINISHED
  </code></pre>
  
  <p>my query as follows </p>
  
  <pre><code>select Count(*) [Count] from employee
  where convert(varchar,FinishDate,112)&gt;='20121001' 
  and convert(varchar,FinishDate,112) &lt;='20121001'  
  and JobState='FINISHED'
  group by SpecialistName
  </code></pre>
  
  <p>if a particular specialist finish multiple jobs in same day then i want to show 1</p>
  
  <p>if robin,josh &amp; Sam finish 10 jobs in same day then 3 will be shown for that day</p>
  
  <p>then output will be like</p>
  
  <pre><code>FinishDate                  Count
  -----------------------     ------
  2012-10-01 00:00:00.000     3     
  2012-10-02 00:00:00.000     5
  2012-10-03 00:00:00.000     15
  </code></pre>
  
  <p>so just guide me how to customize my sql to have desire result. thanks</p>