title: Count number of rows within each month of a datetime, group by year month
body: <p>I'm having trouble getting a sproc to give me this type of output:</p>
  
  <pre>+-----------+------------------+
  |   Month   | NoOfModifcations |
  +-----------+------------------+
  | Jan  2008 |                5 |
  | Nov  2008 |                6 |
  | Feb  2010 |               20 |
  | Jul  2013 |                7 |
  +-----------+------------------+</pre>
  
  <p>So far i managed to get the output to look like this, but i haven't yet managed to get the output to be sorted by year, then month - it's going alphabetically. Here's what i have so far:</p>
  
  <pre><code>SELECT convert(varchar(4), YEAR(LastModifedDate)) + ' ' + convert(varchar(3),datename(month, LastModifedDate)) as Dates,
      count(*) as Number
  
  FROM aims.Modification
  
  WHERE CompanyID = @companyID 
      AND LastModifedDate &gt;= DATEADD(month, @numberOfMonths * -1, GETDATE())
  
  GROUP BY convert(varchar(4), YEAR(LastModifedDate)) + ' ' + convert(varchar(3),datename(month, LastModifedDate))
  </code></pre>
  
  <p>I did try an ORDER BY on the datetime field but got:</p>
  
  <pre><code>Column "aims.Modification.LastModifedDate" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
  </code></pre>
  
  <p>It's also very messy on converting/selecting/group by on the datetime to string. Any advice there would also be very helpful!</p>
  
  <p>Thanks in advance!</p>