SQL Server Performance Forum – Threads Archive

How can I make this query efficient:

I have two tables.
Example data for
Table ATable B
11
11
11
21
22
33
33
34
34
44
44 I would like to measure how similar table A is to table B in the following matter:
For each distict value, i want to count the lower number of occurances between the two tables: For example:
1 appears 3 times in table A and 4 times in table B, so I want to count 3.
2 appears 2 times in table A and 1 time in table B, so I want to count 1.
3 appears 3 times in table A and 2 times in table B, so I want to count 2.
4 appears 2 times in table A and 4 times in table B, so I want to count 2.
I count 8 total. How can I write this query to be efficient as possible?

---

select a,min(a1) from
(select a,count(a) as a1 from a1 group by a
union
select b,count(b) as a1 from b1 group by b ) c group by a

---

I liked the query posted by dineshasanka
adding to it the sum function to give output as 8
select sum(mincnt) from
(select a,min(a1)as mincnt from
(select a,count(a)as a1 from test1
group by a
union all
select a,count(a) from test2
group by a) c group by a) d

---

Thankx ranjitjain,
yeah I fogto to add the sum part.
Anyway thank you

---

]]>