CODEWITHSUNDEEP
mysqlsql
divHelper11
divHelper11

Reputation: 2208

Get total earnings from 3 tables

I have users table and 3 tables with user earnings.
What I am trying to do is selecting the top 10 users with the most earnings based on those 3 tables.
I am struggling with this request since yesterday and I cannot make it to work.
I want to sum those 3 earning tables as total.

Any help on this please?

SELECT 
    users.first_name, 
    users.last_name,
    (select SUM(`value`) from `earnings1`  where users.id = earnings1.user)  as earnings1,
    (select SUM(`value`) from `earnings2`  where users.id = earnings2.user)  as earnings2,
    (select SUM(`value`) from `earnings3`  where users.id = earnings3.user)  as earnings3,
    (earnings1 + earnings2 + earnings3) as total    
FROM users   
GROUP BY users.id
ORDER BY total DESC    
LIMIT 10

The error I am getting right now is:

Unknown column 'earnings1' in 'field list'

Upvotes: 1

Views: 47

Answers (1)

MontyPython
MontyPython

Reputation: 2994

This should work for you:

select first_name, last_name, 
(earnings1 + earnings2 + earnings3) total from
(select users.id, users.first_name, users.last_name,
(select sum(`value`) from `earnings1` where users.id = earnings1.user) as earnings1,
(select sum(`value`) from `earnings2` where users.id = earnings2.user) as earnings2,
(select sum(`value`) from `earnings3` where users.id = earnings3.user) as earnings3
from users group by 1,2,3) t
order by 3 desc limit 10

t is the name you give the resultset from the inner query. it is somewhat similar to a common table expression but not precisely. MySQL mandates you to give this resultset a name.

1,2,3 you can either user 1,2,3 or you can use column names users.id, users.first_name, users.last_name as your grouping columns. 1,2,3 means the first, second and third columns in your select statement.

Upvotes: 1

Related Questions