[SOLVED] MySQL aliasing subqueries

MySQL aliasing subqueries

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.

Solution

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;