Mysql count and sum from two different tables

Question

i have a problem with some querys in php and mysql: I have 2 different tables with one field in common:

table 1

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 i'm 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 the result:

usr_id | tot_h

53 | 353

22 | 156

Thomas · Accepted Answer · 2011-02-28 04:36:21Z

3

There is nothing in your subquery which calculates extra points to correlate it to the outer Table1. So, one solution is to add that correlation:

SELECT usr_id
    , COUNT( id ) + SUM( num_g + hits ) 
    + (SELECT points 
        FROM table2 
        WHERE table2.usr_id = table1.usr_id ) AS tot_h 
FROM table1 
WHERE usr_id != '0' 
GROUP BY usr_id ASC 
LIMIT 0 , 15

Another solution would be to simply join to it directly:

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' 
GROUP BY table1.usr_id ASC 
LIMIT 0 , 15

answered Feb 28, 2011 at 4:36

Thomas

64.8k12 gold badges98 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MarkHu · Accepted Answer · 2011-08-11 19:54:03Z

0

i think get the solution, i dont know if its the best but it works for me, if you know a way to optimize this i really apreciate it.

SELECT usr_id , COUNT( id ) + SUM( num_g + hits )as sumtot ,
      (SELECT points FROM table2  WHERE usr_id = table1.usr_id ) AS tot_h
FROM table1
WHERE usr_id != '0'
GROUP BY usr_id ASC

with this i get something like this

usr_id |sumtot | tot_h

5 |557 | NULL

53 |2217 | 300

So then i just sum the result and show it in a while loop.

<?php
//some mysql here
//then the while loop
// and then the final sum

$final_result=$r_rank['tot_h']+$r_rank['sumtot'];

 ?>

Thanks a lot for your help thomas :)

edited Aug 11, 2011 at 19:54

MarkHu

1,87918 silver badges31 bronze badges

answered Feb 28, 2011 at 7:21

Agent_x

842 silver badges7 bronze badges

Collectives™ on Stack Overflow

Mysql count and sum from two different tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related