title: Count total rows with a group by
body: <p>I have the following query:</p>
  
  <pre><code>select  count(ords.TRACKING_NUM)
  from    Orders ords (NoLock)   
  group by ords.TRACKING_NUM
  having count(distinct ords.ORDER_NUM) = 4
  </code></pre>
  
  <p>I want it to retrieve the total amount of TRACKING_NUMs that have 4 ORDER_NUMs on them (should be 3,352).  Instead I get 3,352 rows equal to 4 (or more because of the distinct).</p>
  
  <p>I get why this is happening.  It is counting the values inside each group by.  And I can easily change the query to this:</p>
  
  <pre><code>select  ords.TRACKING_NUM
  from    Orders ords (NoLock)   
  group by ords.TRACKING_NUM
  having count(distinct ords.ORDER_NUM) = 4
  </code></pre>
  
  <p>and then it get 3,352 rows of TRACKING_NUMs returned to me.  However, this is not very performant on my database (takes about 41 seconds).  What I really need is a query that will give me a count and only a count (and by doing that hopefully go faster).</p>
  
  <p>Thanks for any suggestions.</p>