How to select sum across 100 tables?

I unfortunately have spread some data across about 100 tables, and I don't have time to reorganize it, so I am curious as to how I might do a select sum(column_name) across that many tables.

I have found examples of how to sum across 2开发者_如何学C tables, but never an example of 100.

Anybody know how?

Addendum: Using a very large command has resulted in a "memory exhausted" error. Is there a way to do this using LIKE ?

I would say the easiest way to do this is to do lots of selects (one for each table) of the value you want to sum and just do a sum on the union of that?

Something like :

SELECT SUM(VALUE) FROM (
            select 1 VALUE 
            UNION
            select 2 VALUE
            UNION
            select 3 VALUE) as DATA

Of course the selects will be selecting a column from each table and not just an integer like this, but you get the idea ...

You seem fixated on LIKE, but LIKE is for WHERE clauses, not choosing tables to select from. If selecting from all the tables at once is too much for your system to handle, the obvious solution is to query each table individually and then add up the results using a perl/php/other script.

You can do something like this:

select sum(sumcol) FROM (
    select sum(col) as sumcol from table1
    union all
    select sum(col) as sumcol from table2
    union all
    select sum(col) as sumcol from table3
    ...
    union all
    select sum(col) as sumcol from table100
);

This is done with much less memory consumption when you sum-up in union parts, too.

It would be a big query, but something like this would do it:

SELECT SUM(col) FROM (
  SELECT col FROM table1
  UNION ALL
  SELECT col FROM table2
  UNION ALL
  SELECT col FROM table3
  ...
  SELECT col FROM table100
) a