CODEWITHSUNDEEP
mysqlsqljoinsum
Praveen
Praveen

Reputation: 439

How to join 3 tables and get sum from result of individual joins?


I have the following scenario:
Table: users : user_id, username ,...
Table: login: user_id, login_date, ...
Table: point: user_id, points, point_time
Joins will be on the basis of users.user_id with other tables.
Now, I want to get count of all the logins as well as sum of all the points earned by the user.
Now, when I do:

select users.user_id,count(*) from users
inner join login on users.user_id=login.user_id
group by users.user_id

It returns count as 36(for example). Whenever I run:

select users.user_id,count(*),sum(points) from users
inner join point on users.user_id=point.user_id
group by users.user_id

It returns sum as 400(for example) and count as 2.

But if I combine both the queries:

select users.user_id,count(*),sum(points) from users
inner join login on users.user_id=login.user_id
inner join point on users.user_id=point.user_id
group by users.user_id

It returns count as 72 (36 * 2) and sum as 800 (400 *2). Twice because of multiple userIds present.
I tried several things like combining with distincts but nothing seems to work. Please help.
Better if it's possible with joins alone. Thanks in advance. I am using mysql with Php.

Upvotes: 2

Views: 1735

Answers (2)

Shadwell
Shadwell

Reputation: 34774

You should be able to do your count by joining in your login table and then including a subquery to get your count of points:

select users.user_id, count(*) as login_count,
  (select sum(points) from point 
   where point.user_id = users.user_id) as points_sum 
from users
inner join login on users.user_id=login.user_id
group by users.user_id

Upvotes: 1

Rednaxel
Rednaxel

Reputation: 968

You can sum the points in a subquery and select distinct logins in the count

select users.user_id,l.login,p.points from users
inner join (select user_id, count(1) login from login
       group by login) as l on users.user_id=login.user_id
inner join (select user_id, sum(point) as point 
        from point group by user_id ) as p on users.user_id=point.user_id

Upvotes: 3

Related Questions