CODEWITHSUNDEEP
mysqlsortingsum
Damien
Damien

Reputation: 157

MySQL How do I get the sum of multiple rows from multiple tables and then order the results by total descending?

I am trying to build a leaderboard based on the points (money) a user has. The points are stored in multiple tables and some users may not have points in a given table.

Table: account
--------------------------
| uid | name    | locker |
|-----|---------|--------|
| 1   | Bob     | 15     |
| 2   | Dave    | 2      |
| 3   | Jim     | 5      |
--------------------------

Table: container
------------------------
| account_uid | money  |
|-------------|--------|
| 1           | 4      |
| 3           | 1      |
| 3           | 2      |
| 3           | 4      |
------------------------

Table: vehicle
------------------------
| account_uid | money  |
|-------------|--------|
| 2           | 2      |
| 2           | 1      |
| 3           | 2      |
------------------------

I would like to see the results ouput as -

Bob 19
Jim 14
Dave 5

Note that some tables do not have points for some people.

This code did not work for me. It seems to have duplicated the points somehow.

SELECT
  act.name,
  act.uid,
  SUM(COALESCE(act.locker,0) + COALESCE(con.money,0) + COALESCE(veh.money,0)) AS total
FROM account as act
  LEFT JOIN container as con
    ON act.uid = con.account_uid
  LEFT JOIN vehicle as veh
    ON act.uid = veh.account_uid
Group By act.name
ORDER BY total DESC

Upvotes: 0

Views: 34

Answers (1)

Osuwariboy
Osuwariboy

Reputation: 1375

How about calculating the totals one at a time and then combining them into one? 

select account.uid, account.name, sum(tot.Money) as TotalMoney from
(
    (select uid, sum(Money) as Money from container
    group by uid)
    union all
    (select uid, sum(Money) as Money from vehicle
    group by uid)
) tot
inner join account on 
tot.uid = account.uid
group by account.uid

Upvotes: 1

Related Questions