Mysql count and sum from two different tables -

i have problem querys in php , mysql: have 2 different tables 1 field in common:

table 1

id | hits | num_g | cats | usr_id |active

1 | 10 | 11 | 1 | 53 | 1

2 | 13 | 16 | 3 | 53 | 1

1 | 10 | 22 | 1 | 22 | 1

1 | 10 | 21 | 3 | 22 | 1

1 | 2 | 6 | 2 | 11 | 1

1 | 11 | 1 | 1 | 11 | 1

table 2

id | usr_id | points

1 | 53 | 300

---

now use statement sum total table 1 every id count + 1 too

select usr_id, count( id ) + sum( num_g + hits ) tot_h table1 usr_id!='0' group usr_id asc limit 0 , 15 

and total each usr_id

usr_id| tot_h |

53 | 50

22 | 63

11 | 20

until here ok, have second table points (table2) try this:

select usr_id, count( id ) + sum( num_g + hits ) + (select points table2 usr_id != '0' ) tot_h table1 usr_id != '0' group usr_id asc limit 0 , 15 

but seems sum 300 points users:

usr_id| tot_h |

53 | 350

22 | 363

11 | 320

now how can total first try + secon table in 1 statement? because have 1 entry in second table can more there. help.

---

hi thomas reply, think in right direction, i'm getting weirds results,

usr_id | tot_h

22 | null <== think null because usr_id no value in table2

53 | 1033

its second user getting the values. try one:

      select table1.usr_id, count( table1.id ) + sum( table1.num_g + table1.hits + table2.points ) tot_h table1 left join table2 on table2.usr_id = table1.usr_id table1.usr_id != '0' , table2.usr_id = table1.usr_id group table1.usr_id asc           

same result sum of values , not each user, need result:

usr_id | tot_h

53 | 53 <==== plus 300 points on table1

22 | 56 <==== plus 100 points on table2

/////////the result need ////////////

usr_id | tot_h

53 | 353 <==== plus 300 points on table2

22 | 156 <==== plus 100 points on table2

i think structure need pseudo statements ;)

from table1 count id number of record usr_id sum hits + num_g , table2 select points usr_id same table1 , result:

usr_id | tot_h

53 | 353

22 | 156

there nothing in subquery calculates points correlate outer table1. so, 1 solution add correlation:

select usr_id     , count( id ) + sum( num_g + hits )      + (select points          table2          table2.usr_id = table1.usr_id ) tot_h  table1  usr_id != '0'  group usr_id asc  limit 0 , 15 

another solution join directly:

select table1.usr_id     , count( table1.id )          + sum( table1.num_g + table1.hits + table2.points )          tot_h  table1      left join table2         on table2.usr_id = table1.usr_id table1.usr_id != '0'  group table1.usr_id asc  limit 0 , 15