title: SQL Server: combine multiple counts in one select
body: <p>I am pretty new to SQL and am looking for help with the following: </p>
  
  <p>I would like to count the number of items in column "esc" for the current + the previous 5 months and would like to have this done in one query. 
  All I need is the number for each of this 6 months. </p>
  
  <p>My SP (not working): </p>
  
  <pre><code>SELECT
      (SELECT COUNT(*) FROM Log_Esc WHERE CONVERT(DATE, dateEsc, 120) &gt;= CONVERT(DATE, CONVERT(VARCHAR(6), GETDATE(), 112) + '01', 112)),
      (SELECT COUNT(*) FROM Log_Esc WHERE CONVERT(DATE, dateEsc, 120) &gt;= CONVERT(DATE, CONVERT(VARCHAR(6), DATEADD(month, -1, GETDATE()), 112) + '01', 112)),
      (SELECT COUNT(*) FROM Log_Esc WHERE CONVERT(DATE, dateEsc, 120) &gt;= CONVERT(DATE, CONVERT(VARCHAR(6), DATEADD(month, -2, GETDATE()), 112) + '01', 112)),
      (SELECT COUNT(*) FROM Log_Esc WHERE CONVERT(DATE, dateEsc, 120) &gt;= CONVERT(DATE, CONVERT(VARCHAR(6), DATEADD(month, -3, GETDATE()), 112) + '01', 112)),
      (SELECT COUNT(*) FROM Log_Esc WHERE CONVERT(DATE, dateEsc, 120) &gt;= CONVERT(DATE, CONVERT(VARCHAR(6), DATEADD(month, -4, GETDATE()), 112) + '01', 112)),
      (SELECT COUNT(*) FROM Log_Esc WHERE CONVERT(DATE, dateEsc, 120) &gt;= CONVERT(DATE, CONVERT(VARCHAR(6), DATEADD(month, -5, GETDATE()), 112) + '01', 112))
  FROM Log_Esc
  FOR XML PATH('total'), ROOT('ranks')
  </code></pre>
  
  <p>Can someone tell me how I have to change this to get it working ?</p>
  
  <p>Many thanks for any help with this, Mike</p>