how can i get the total from the four different table in single query

In each table have many number of rows with amount. Like this i have different numbers of amo开发者_如何学JAVAunt in each table. Each tables field name is different. How can i get the total of all values in the four tables in a single query? Any way is there?

Did you try something like this?

select sum(val) from (
    select sum(col1) as val from table1 where criteria1 = 'x'
    union all
    select sum(col2) from table2 where criteria2 = 'y'
    union all
    select sum(col3) from table3 where criteria3 = 'z'
    union all
    select sum(col4) from table4 where criteria4 = 'w'
) newTbl

use derived tables - u probably want to do something like this so you get all results in a single row !

select
  u.user_count,
  c.country_count
from
(
  select count(*) as user_count from users where username like 'f%'
) u
join
(
  select count(*) as country_count from country
) c;

a more comlpex example: http://pastie.org/921407