title: SQL count date range
body: <p>I'm trying to query my SQL database to get the number of orders made by each client within a certain date range.</p>
  
  <p>I have a list of orders as follows</p>
  
  <pre><code>CustomerName       ClientID        DateOrdered
  Customer No.2         10            2011-11-25
  Customer No.3         11            2011-10-15
  Customer No.3         11            2011-11-25
  </code></pre>
  
  <p>and I want to be able to find out how many orders have been made by a specific client for example between 2011-11-1 and 2011-11-30, this should result in :</p>
  
  <pre><code>CustomerName       ClientID        Number
  Customer No.3         11             1
  Customer No.2         10             1
  </code></pre>
  
  <p>So far I've managed to get this </p>
  
  <pre><code>SELECT CustomerName, ClientID, COUNT(*) AS Number      
  FROM Orders t     
  GROUP BY CustomerName, ClientID      
  HAVING COUNT(*) =      
  (SELECT MAX(Number)         
  FROM          
  (SELECT CustomerName, ClientID, COUNT(*) AS Number             
  FROM Orders            
  GROUP BY CustomerName, ClientID ) x       
  WHERE CustomerName = t.CustomerName )
  </code></pre>
  
  <p>Which gives me every order the customer has ever made</p>
  
  <pre><code>CustomerName       ClientID        Number
  Customer No.3         11             2
  Customer No.2         10             1
  </code></pre>
  
  <p>Am I going about the right way to solve this or is there a simpler way which I've completely overlooked!</p>