CODEWITHSUNDEEP
mysqlsql
unglued
unglued

Reputation: 1051

MySQL sort by sum multiple columns in different tables

I have 3 tables:

Users


| id | name  |
|----|-------|
| 1  | One   |
| 2  | Two   |
| 3  | Three |

Likes


| id | user_id | like  |
|----|---------|-------|
| 1  | 1       | 3     |
| 2  | 1       | 5     |
| 3  | 2       | 1     |
| 4  | 3       | 2     |

Transations


| id | user_id | transaction |
|----|---------|-------------|
| 1  | 1       | -1          |
| 2  | 2       | 5           |
| 3  | 2       | -1          |
| 4  | 3       | 10          |

I need get sum of likes.like and transations.transation for each user and then sort it by its result.

I was able to do it for users and likes:

select users.*, sum(likes.like) as points
from `users`
inner join `likes` on `likes`.`user_id` = `users`.`id`
group by `users`.`id`
order by points desc

But then I add transactions table like this:

select users.*, (sum(likes.like)+sum(transactions.`transaction`)) as points
from `users`
inner join `likes` on `likes`.`user_id` = `users`.`id`
inner join `transactions` on `transactions`.`user_id` = `users`.`id`
group by `users`.`id`
order by points desc

It is show wrong results.

I expecting to see:

| id | name  | points |
|----|-------|--------|
| 3  | Three | 12     |
| 1  | One   | 7      |
| 2  | Two   | 5      |

But get this instead:

| id | name  | points |
|----|-------|--------|
| 3  | Three | 12     |
| 1  | One   | 6      |
| 2  | Two   | 5      |

So, how sort users by sum likes.like and transations.transation?

Thank you!

Upvotes: 1

Views: 308

Answers (1)

TobyLL
TobyLL

Reputation: 2296

Since there's not a 1-to-1 relationships between transactions and likes, I think you need to use subqueries:

select users.*,
    (select sum(points) from likes where user_id = users.id) as points,
    (select sum(transaction) from transactions where user_id = users.id) as transactions
from users
order by points desc

Updated after more explanation of requirements:

select users.*,
    (select sum(points) from likes where user_id = users.id) +
    (select sum(transaction) from transactions where user_id = users.id) as points
from users
order by points desc

Upvotes: 1

Related Questions