Equivalent to doing a join after the group by

Question

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?

@PareshJadhav Yes (SQL server 2014 if it makes a difference) — soandos, Nov 05 '14 at 11:24
On merging, you have added condition, s1.date = s2.date which will eliminate rows from both tables and shows result that are match. I will suggest you to use Union of two queries. Also, add a hardcoded column 'S1' and 'S2' to identify which row belongs to which table after union. — Paresh J, Nov 05 '14 at 11:27
@PareshJadhav So I considered using union, but then I'd get two rows for each data, and then i'd need an extra column in each, do a group by, with a group concat. Is there a way to do it without all of that? — soandos, Nov 05 '14 at 11:28

Thiago Lunardi · Accepted Answer · 2014-11-05T11:33:12.773

2

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.

edited Nov 05 '14 at 11:33

answered Nov 05 '14 at 11:23

Thiago Lunardi

749
1
5
19

So this is much better than mine (~50% performance increase), but still has a join. Is there a way to get rid of that? – soandos Nov 05 '14 at 11:25
I added a second query without using joins. See if it fits for you. – Thiago Lunardi Nov 05 '14 at 11:33
I think the inner query requires a from clause (pretty sure thats the only error) – soandos Nov 05 '14 at 11:35
Thank you, this is much better. – soandos Nov 05 '14 at 11:39

score 1 · Answer 2 · answered Nov 05 '14 at 11:37

This is the query that do the job with just one select:

select top 100 date, 
count(*) as count_all,
sum (
  case 
    when content not like '%some condition%' then 1
    else 0
  end
) as count_condition
from sections
group by date
order by date

I am also pasting a working snippet from AdventureWorks2012 database

select top 100 
ModifiedDate, 
count(*) as count_all,
sum (
case when CarrierTrackingNumber not like '4911%' then 1
else 0
end
) as count_condition
from [Sales].[SalesOrderDetail]
group by ModifiedDate
order by ModifiedDate

For you reference you can use subqueries after FROM clause in SQL Server.

Not sure why I thought I couldn't before. Thank you. – soandos Nov 05 '14 at 11:39 — soandos, Nov 05 '14 at 11:39

Equivalent to doing a join after the group by

2 Answers2