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