sql-servergroup-byinner-query

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?


Solution

  • 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.