How to get count of single and multiple matches in a single SQL query

Question

Assume I have following 2 tables,

table1
-----------
col1 | col2
-----------
a1      b1
a2      b2
a3      b2
a4      b4
a1      b1

table2
-----------
col1 | col2
-----------
a1      b1
a2      b3
a2      b2
a4      b4

In the above example, there is one record in table2 (a1,b1) which appears more than once in table1(2 in this case) and two records in table2((a2,b2) and (a4,b4)) that appears exactly once in table1. I want number of records in table2 that has exact one match in table 1 and number of records in table2 that has more than 1 match in table1. How do I get this result in a single query. Something like below:-

countOfSingleMatch | countOfMultipleMatch

    2                    1

I could do it with separate queries but I wanted to achieve this in a single query.

Answer 1

I think what you want conceptually is a tally of the number of times a record in table2 matches to another record in table1. Since you don't need to report the number of a times a match does not take place, we can start off by considering an INNER JOIN between the two tables. Then, we can aggregate by col1 and col2 and count the number of matches. Finally, pass over this as a subquery and count the number of times each type of match happens.

SELECT
    SUM(CASE WHEN t.cnt = 1 THEN 1 ELSE 0 END) AS countOfSingleMatch,
    SUM(CASE WHEN t.cnt > 1 THEN 1 ELSE 0 END) AS countOfMultipleMatch
FROM
(
    SELECT t2.col1, t2.col2, COUNT(*) cnt
    FROM table2 t2
    INNER JOIN table1 t1
        ON t1.col1 = t2.col1 AND t1.col2 = t2.col2
    GROUP BY t2.col1, t2.col2
) t

Output:

Demo here:

Rextester