UNION ALL GROUP BY

[Dámaso Velázquez Álvarez]

I have:

SELECT x, count(x)
FROM table1
WHERE ...

UNION ALL

SELECT x, count(x)
FROM table2
WHERE...

UNION ALL

SELECT x, count(x)
FROM table3
WHERE ...

UNION ALL
....
....

and I need to do GROUP BY with all the sentences.

is it possible? Thanks.

[Bill Karwin]

"Dámaso Velázquez Álvarez" <dv********@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

I have:

SELECT x, count(x)
FROM table1
WHERE ...

UNION ALL

SELECT x, count(x)
FROM table2
WHERE...

UNION ALL

SELECT x, count(x)
FROM table3
WHERE ...

UNION ALL
...
...

and I need to do GROUP BY with all the sentences.

is it possible? Thanks.

One easier way to do this would be to use a derived table in a subquery in
the FROM clause:

SELECT x, COUNT(x)
FROM (
SELECT x FROM table1
UNION ALL
SELECT x FROM table1
UNION ALL
SELECT x FROM table1
)
GROUP BY x

This requires MySQL 4.1 or later, for support of subqueries.

If you cannot use subqueries, I think you need to do the SQL query without
the group by or the count(), and instead calculate the aggregate counting in
your application code.

Regards,
Bill K.