Question 412365 in Mysql Question posted in
The official MySQL documentation can be found here.

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
Tags:

2

Answers


  1. 0

    You simply need to join all the table and need to put final calculation at the final select statement like below –

    SELECT U.usersid, COALESCE(ROUND(job_count/job_offer_count*total_pay), 0) user_score, name
  FROM USERS U
  LEFT JOIN (SELECT usersid, SUM(totalPay) total_pay
               FROM LESSONS
              GROUP BY usersid) L ON U.usersid = L.usersid
  LEFT JOIN (SELECT usersid, COUNT(*) job_count
               FROM JOBS
              GROUP BY usersid) J ON U.usersid = J.usersid
  LEFT JOIN (SELECT usersid, COUNT(*) job_offer_count
               FROM JOB_OFFERS
              GROUP BY usersid) JO ON U.usersid = JO.usersid;

    Demo.

    Login or Signup to reply.
  2. 0

    This is an SQL that you try to write based on your needs, but since there is no DDL, the corresponding table is not created for testing

    select
    usersid,
    name,
    if (
            greens > 0 and golds > 0,
            round((greens/golds)*totalSum),
            0
    ) as user_score
from (
         select
             u.usersid,
             u.name,
             sum(ifnull(l.totalPay, 0)) as totalSum,
             ifnull(count(distinct j.jobsid), 0) as greens,
             ifnull(count(distinct o.offersid), 0) as golds
         from users u
                  left join lessons l on l.usersid = u.usersid
                  left join jobs j on j.usersid = u.usersid
                  left join offers o on o.usersid = u.usersid
         group by usersid
) mid
order by user_score desc
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search