title: Equivalent to doing a join after the group by
body: <p>I am looking to merge the following two queries into one:</p>
  
  <pre><code>select top 100 date, count(*) from sections
  where content not like '%some condition%'
  group by date
  order by date;
  
  select top 100 date, count(*) from sections
  group by date
  order by date;
  </code></pre>
  
  <p>It is like this question, <a href="https://stackoverflow.com/questions/9390679/left-join-after-group-by">LEFT JOIN after GROUP BY?</a> except that I need this to work for MS SQL Server, not MySQL (the difference being that MSSQL does not allow subqueries in the from clause).</p>
  
  <p>I am looking for a way to have the result set have three columns, <code>date</code>, the first <code>count(*)</code>, and the second <code>count(*)</code>.</p>
  
  <p>My current solution is:</p>
  
  <pre><code>select top 100 date, 
  (select count(*) from sections s1
  where content not like '%some condition%' 
  and s1.date = s2.date),
  (select count(*) from sections s1
  where s1.date=s2.date) from sections as s2
  group by date
  order by date;
  </code></pre>
  
  <p>Is there a better way to do this?</p>