how to count multi table with same columns?

xxx12345

I have a database with a lot of tables, But all of
them have same column setup, Looks like this:

table1.ip table2.ip table3.ip....

every table have an lot of rows.

my questions is , How can I count them by group?
for 1 table, I know I can use
select ip,count(ip) from table1 group by ip;

But how can I count all ip in table1,table2....And group
them?

I know I can use an temp table to do this, But it cost
so many time....

Thanks.

Sxooter

The normal way is to use a union enclosed in a subselect in a view:

create view bigview as
select from table1
union
select from table2
union
select from table3
...
union
select from table99

Then just call the view:

select count(fieldname) from bigview where ...

Hope that helps.

Sxooter

Note, you can also do this with a subselect, but I'm a little rusty on the SQL syntax, and don't want to post a non-working query if I can help it. My kids are using this PC as a game machine most of today, so I don't have access to postgresql to test the subselect method, but it SHOULD go something like:

select count(field1) from (
select from table1
UNION
select from table2
UNION
)

but that may need an alias to work right.

If you're using MySQL you'll just have to stick to the whole temp table thing, I'm not sure you can do this there.