CODEWITHSUNDEEP
sqlsql-server-2008
Sid M
Sid M

Reputation: 4364

Compare table and find elements with not matching count

I've two tables

Table1

enter image description here

And Table2

enter image description here

Now I want those RequestId and the count of those RequestId from Table1 which differ from that of Table2, For Example, Output should be

enter image description here

I can get individual count and RequestId from both the tables by the following query:

select RequestId, Count(RequestId) AS [Count] from Table1 group by RequestId

But how to compare both tables in a single query, Any help will be good and it would be good if no looping is performed until its the only way of doing it, as there are many records in both the tables, images shared here are just for understanding.

Upvotes: 0

Views: 39

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95082

You'd use your queries as subqueries in FROM. Outer join table2, because there ain't rows for all request IDs in it:

select t1.requestid, t1.cnt - coalesce(t2.cnt, 0) as diff
from (select requestid, count(*) as cnt from table1 group by requestid) t1
left join (select requestid, count(*) as cnt from table2 group by requestid) t2
  on t2.requestid = t1.requestid
where (t1.cnt - coalesce(t2.cnt, 0)) > 0;

Upvotes: 1

Raffaello.D.Huke
Raffaello.D.Huke

Reputation: 552

If i didn't get you wrong then you can just use your query to check the count of RequestId in each tables and then join them by the count not even. and do the simple math:

select t1.RequestId, (t1.Count - t2.Count) as count 
 from
 (select RequestId, Count(RequestId) AS [Count] from Table1 group by RequestId)t1
left join(select RequestId, Count(RequestId) AS [Count] from Table2 group by RequestId)t2
on t1.RequestId = t2.RequestId  
where t1.Count <> t2.Count

Upvotes: 0

Related Questions