how to get SUM from two different tables that are linked to third one

ok the problem is that I have to sum two sums from two tables that are linked

first table points:

id | user_id | point | hit
1  | 1       |     4 | yes
2  | 1       |     2 | yes
3  | 1       |    -3 | no
4  | 1       |     4 | mb
5  | 2       |     2 | yes
6  | 2       |    -3 | no
7  | 2       |     4 | mb

seccond table earnings:

id | user_id | earning
1  | 1       |   5      
2  | 1       |   2     
3  | 1       |   3     
4  | 1       |   4     
5  | 2       |   2    
6  | 2       |   3    
7  | 2       |   4  

now what I've tried is this:

SELECT 
    p.id, 
    SUM( SUM(p.point) + SUM(e.earning) ) AS bb 
FROM 
    points p LEFT JOIN earnings e ON p.user_id = e.user_id 
WHERE 
    (p.hit = "yes" OR p.hit = "no") 
GROUP BY 
    p.user_id

but I got wrong resu开发者_JAVA技巧lts

I want to get this:

id | bb
1  | 17
2  | 8

Thanks!

If you want total earnings per user, you should probably start by identifying which users you want earnings for. The most sensible option is probably to use the users table itself as the basis.

SELECT user_id,
       (SELECT SUM(point)
          FROM points
         WHERE user_id = u.user_id
       )
       +
       (SELECT SUM(earning)
          FROM earnings
         WHERE user_id = u.user_id
           AND p.hit IN ('yes', 'no')
       ) AS bb
  FROM users

This approach has the added benefit of reporting on users who had no points and/or earnings.

maybe something like:

select goals.user_id, sum(goal) as goal from
(select
  user_id, point as goal from points p where  (p.hit = "yes" OR p.hit = "no") 
union all
select
  user_id, earning as goal from earnings) goals
group by goals.user_id

Try formatting your bb field like this:

SUM(p.point + e.earning)

Small modification to ur code...assuming the id in both fields are not relevant...and ur trying to select user_id and not id

SELECT p.user_id, SUM(p.point) + SUM(e.earning) AS bb 
FROM points p JOIN earnings e ON p.user_id = e.user_id 
WHERE (p.hit = "yes" OR p.hit = "no") 
GROUP BY p.user_id

The problem of you query is that with the join on the user field, you will get multiple rows... You have to do the sums and then put the data together, unless you have a 1-1 relationship between points and earnings tables (like the id)

A solution could be to create the table with the two sums and join them to get your data

select COALESCE(P.user_id, E.user_id), points, earnings, 

sum(COALESCE(points,0) + COALESCE(earnings,0)) from

(select user_id, sum(point) as points from points 

where (p.hit = "yes" OR p.hit = "no") group by user_id) as P

full outer join

(select user_id, sum(earning) as earnings from earnings group by user_id) as E
on P.user_id = E.user_id

group by COALESCE(P.user_id, E.user_id)

I used FULL OUTER JOIN and coalesce to be sure that data are provided eve if one of the table is missing. If this is not necessary, you could alter your join condition (to a LEFT OUTER JOIN if, for example, you need data only whenere there is at least one valid point)

Try changing this

SUM( SUM(p.point) + SUM(e.earning) )

to

SUM(p.point) + SUM(e.earning)