MySQL aliasing subqueries

Question

I have a table consisting of two columns only: id1 and id2. The following query in MySQL works just fine:

(select id1 as id, count(id1) as cnt
from my_table 
group by id1)
union all
(select id2 as id, count(id2) as cnt
from my_table
group by id2)

If I want to use above query as a subquery, I need to alias every derived table - below code gives an error ("every derived table must have it’s own alias"):

select id, cnt from
(select id1 as id, count(id1) as cnt
from my_table 
group by id1)
union all
(select id2 as id, count(id2) as cnt
from my_table
group by id2)

But I can't find out the correct syntax to alias the derived tables. I was experimenting with adding and removing parenthesis, but no luck so far. For below query I just get generic SQL syntax error:

select id, cnt from
(select id1 as id, count(id1) as cnt
from my_table 
group by id1) as tab1
union all
(select id2 as id, count(id2) as cnt
from my_table
group by id2) as tab2

Later on I will want to do something more with this subquery rather than only selecting id and cnt, but it's simplified scenario to find correct syntax for aliasing subqueries.

Answer 1

Try below: since you union all the result so you need just one alias

SELECT id, cnt
FROM
(
    SELECT id1 AS id, COUNT(id1) AS cnt
    FROM my_table 
    GROUP BY id1
    UNION ALL
    SELECT id2, COUNT(id2)
    FROM my_table
    GROUP BY id2
) AS tab;

Answer 2

Just to round out this question, here is the syntax you need to union two derived tables:

SELECT id, cnt
FROM
(
    SELECT id1 AS id, COUNT(id1) AS cnt
    FROM my_table 
    GROUP BY id1
) AS tab1
UNION ALL
SELECT id, cnt
FROM
(
    SELECT id2 AS id, COUNT(id2) AS cnt
    FROM my_table
    GROUP BY id2
) AS tab2;

Demo

You need explicit select statements for each component of the subqueries in the UNION. Of course, the answer by @fa06 is what you should use, but if you needed to go down this road, the above is how you would make it work.