Create a MySQL statement joining multiple tables and doing a calculation after retrieving SUM, COUNT or NULL results

I am trying to create a SQL statement that will allow me to paginate users calculated performance results. Any help would be greatly appreciated. Here is a mock up of what I am trying to do:

I have 4 tables..

Users

usersid | name etc...
1 | Satoshi
2 | Musk

Lessons with amounts

lessonsid | usersid | totalPay
1 | 1 | 200
2 | 1 | 375

Jobs

jobsid | usersid..
1 | 1
2 | 1
3 | 1
4 | 1
5 | 1

Job offers

offersid | usersid..
1 | 1
2 | 1
3 | 1
4 | 1
5 | 1
6 | 1
7 | 1
8 | 1
9 | 1

I need to do as described in the logic below

SELECT users.*, 
  'get all users', 
  'The SUM(totalPay) FROM lessons, if there are no lessons then return 0 - AS totalSum',
  'The COUNT(jobid) FROM jobs WHERE usersid=id, if there are no jobs then return 0 - AS greens',
  'The COUNT(id) FROM offers WHERE usersid=id, if there are no offers then return 0 - AS golds'

FROM
  users

LEFT JOIN
  'Left join tables USING usersid? I need all the data from the users table regardless of whether there is data available in the joined tables. If there is no data in the joined tables then variables need to have a zero int value as in the SELECT statement'
  'lessons'
  'jobs'
  'offers'

DO CALCULATION - Calculate a score for the user (I have used php as an example)
  'user_score = 0;
  if (greens>0 && golds>0){
     user_score = (greens/golds) * totalSum;
     user_score = round(user_score);
  }'

ORDER BY user_score DESC

The desired result should show the following;

userid: 1 greens=5, golds=9, totalsum=575 -> user_sore=5/9*575=319
userid: 2 user_sore=0


usersid | user_score | name etc
1 | 319 | Satoshi
2 | 0 | Musk

You need to sign in to view this answers