SQL select multiplicity of items in union

Question

I am performing several selects and wish to compute the overlap. Example:

SELECT id FROM foo WHERE ...
SELECT id FROM bar WHERE ...
SELECT id FROM baz WHERE ...

Call these queries a, b, and c, respectively. Suppose a gives (1,2,3,4,5), b gives (1,3,5), and c gives (4,5,6). I want to take the union of these and count the multiplicities. For the example above, the result I am looking for is

id | multiplicity
-----------------
1  | 2
2  | 1
3  | 2
4  | 2
5  | 3
6  | 1

How do I do this in MySQL5 within one query? (The a, b, and c parts may be plain selects or stored procedures).

Answer 1

I cannot verify this at the moment, but I believe this will work

SELECT id, count(id) AS multiplicity 
FROM
(
    SELECT id FROM foo WHERE ...
    UNION ALL
    SELECT id FROM bar WHERE ...
    UNION ALL
    SELECT id FROM baz WHERE ...
) AS TablesTogether
GROUP BY id