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

Question

How do I create a SQL statement to paginate users calculated performance results?

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

Query description:

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

Desired result:

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

Ankit Bajpai · Accepted Answer · 2024-10-23 08:05:28Z

1

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.

answered Oct 23, 2024 at 8:05

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jarlh Over a year ago

GROUP BY before JOIN - the safe way!

Bobby Kalia Over a year ago

Brilliant, works great thanks!

null · Accepted Answer · 2024-10-24 01:38:52Z

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,
             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 (select usersid, sum(totalPay) totalPay from lessons group by usersid) 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

edited Oct 24, 2024 at 1:38

answered Oct 23, 2024 at 8:16

null

894 bronze badges

2 Comments

Barmar Over a year ago

The SUM() will be multiplied by the number of matches in the cross product of all the tables.

null Over a year ago

@Barmar You're right, for totalSum have to use a subquery before joining the table

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Linked

Related