Q:

Equivalent to doing a join after the group by

I am looking to merge the following two queries into one:
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;

It is like this question, LEFT JOIN after GROUP BY? 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).
I am looking for a way to have the result set have three columns, date, the first count(*), and the second count(*).
My current solution is:
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;

Is there a better way to do this?

A:

Try this:
with q1 as (select top 100 date, count(*) total from sections
            where content not like '%some condition%'
            group by date),
q2 as (select top 100 date, count(*) total from sections
       group by date)
select q1.date, q1.total total1, q2.total total2
  from q1
  join q2 on q1.date = q2.date
  order by q1.date

UPDATE:
Or this:
select date,
       count(*) total,
       sum(has_condition) total_condition
from (select top 100 
             date, 
             case when content not like '%some condition%' then 1
                  else 0 end has_condition
        from sections ) t
group by date
order by date;

I did not do any triout, but that is the idea.

