php - collect data from multiple tables with count and sum -

select sum(amount) amount , count(userid) total table1 userid='1111'  union  select sum(amount) amount,  count(userid) total table2 userid='1111' union  select sum(amount) amount,  count(userid) total table3 userid='1111' union  select sum(amount) amount,  count(userid) total table4 userid='1111' union  select sum(amount) amount,  count(userid) total table5 userid='1111' 

i found somewhere not working me .

proposed solution:

the other option data tables separately , display .

question:

i want data multiple tables single query. amount added , userid counted tables.

additional notes: should gather info user id = 1111 , save in 2 varibables.

userid= 1111 , may not present in tables.

expect out this:

your total : $total | , amount : $amount

based on suggestions. here have acheieved far , how trying display it. displays nothing without errors: if use single query, results displayed fine.

<?php $sqltotal=mysql_query("select sum(data.amount) amount ,sum(data.total) total     (      select coalesce(sum(amount),0) amount , coalesce(count(userid)) total table1  userid='1111'       union      select coalesce(sum(amount),0) amount,  coalesce(count(userid)) total table2 userid='1111'      union      select coalesce(sum(amount),0) amount,  coalesce(count(userid)) total table3 userid='1111'      union      select coalesce(sum(amount),0) amount,  coalesce(count(userid)) total table4 userid='1111'      union        select coalesce(sum(amount),0) amount,  coalesce(count(userid)) total table5  userid='1111'   ) data "); while($row = mysql_fetch_array($sqltotal))   { echo "$row[total]"; echo "$row[sum]"; }  //echo "$yourtotal: $row[total]";  ?> 

if need single row tables sum column amount outer select on union. mentioned in comment may of tables may not contain userid need use coalesce make sure if null 0 selected value.

select sum(coalesce(data.amount,0)) amount ,sum(coalesce(data.total,0)) total     (      select coalesce(sum(amount),0) amount , coalesce(count(userid)) total table1 userid='1111'        union      select coalesce(sum(amount),0) amount,  coalesce(count(userid)) total table2 userid='1111'       union      select coalesce(sum(amount),0) amount,  coalesce(count(userid)) total table3 userid='1111'       union      select coalesce(sum(amount),0) amount,  coalesce(count(userid)) total table4 userid='1111'       union        select coalesce(sum(amount),0) amount,  coalesce(count(userid)) total table5 userid='1111'   ) data 

and if want use join need use left join , need sum separately each table , add main total.