Mysql count and sum from two diferent tables
- by Agent_x
Hi all, i have a problem with some querys in php and mysql:
I have 2 diferent tables with one 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 i use this statement to sum just the total from the table 1 every id count + 1 too
SELECT usr_id, COUNT( id ) + SUM( num_g + hits ) AS tot_h FROM table1 WHERE usr_id!='0' GROUP BY usr_id ASC LIMIT 0 , 15
and i get the total for each usr_id
usr_id| tot_h |
53 | 50
22 | 63
11 | 20
until here all is ok, now i have a second table with extra points (table2)
I try this:
SELECT usr_id, COUNT( id ) + SUM( num_g + hits ) + (SELECT points FROM table2 WHERE usr_id != '0' ) AS tot_h FROM table1 WHERE usr_id != '0' GROUP BY usr_id ASC LIMIT 0 , 15
but it seems to sum the 300 extra points to all users:
usr_id| tot_h |
53 | 350
22 | 363
11 | 320
Now how i can get the total like the first try but + the secon table in one statement? because now i have just one entry in the second table but i can be more there.
thanks for all the help.
===============================================================================
hi thomas thanks for your reply, i think is in the right direction, but im getting weirds results, like
usr_id | tot_h
22 | NULL <== i think the null its because that usr_id as no value in the table2
53 | 1033
Its like the second user is getting all the the values. then i try this one:
SELECT table1.usr_id, COUNT( table1.id ) + SUM( table1.num_g + table1.hits + table2.points ) AS tot_h
FROM table1
LEFT JOIN table2 ON table2.usr_id = table1.usr_id
WHERE table1.usr_id != '0'
AND table2.usr_id = table1.usr_id
GROUP BY table1.usr_id ASC
Same result i just get the sum of all values and not by each user, i need something like this result:
usr_id | tot_h
53 | 53 <==== plus 300 points on table1
22 | 56 <==== plus 100 points on table2
/////////the result i need ////////////
usr_id | tot_h
53 | 353 <==== plus 300 points on table2
22 | 156 <==== plus 100 points on table2
I think the structure need to be something like this
Pseudo statements ;)
from table1 count all id to get the number of record where the usr_id are then sum hits + num_g and from table2 select the extra points where the usr_id are the same as table1 and get teh result:
usr_id | tot_h
53 | 353
22 | 156
