Mysql select from subquery is not working

debugcn Published at Dev

Fury

I want to create report of total number of orders and total earning that are belong to each users.

SELECT w.id, CONCAT_WS(' ', w.fname, w.sname) AS full_name, 
       te.total_earnings, te.assigned_jobs
FROM users AS w
LEFT JOIN (
    SELECT wr.user_id,
           COUNT(o.order_id) AS assigned_jobs,
           SUM(o.fee) AS total_earnings
    FROM (
        SELECT DISTINCT user_id, order_id, withdrawn
        FROM work_records
    ) AS wr
    LEFT JOIN orders o ON o.order_id = wr.order_id
    WHERE wr.withdrawn IS NULL
      AND o.verified != 'rejected'
) AS te ON te.user_id = w.id
WHERE w.status = 1


orders                      work_records
___________________      _________________________________
| order ID | fee  |      | id | order_id | fee | user_id |
-------------------      ---------------------------------
|   334    | 425  |      |  1 |   334    | 50  |    6    |
                         |  2 |   334    | 50  |    6    |

This query works on single user id. But it doesn't work if I want to get report of all users.

Any advise thanks?

Fury

Here is the answer for others. How ever the query is slower. But if you have faster query would greate to share.

SELECT w.id, CONCAT_WS(' ', w.fname, w.sname) AS full_name, 
       te.total_earnings, te.assigned_jobs
FROM users AS w
LEFT JOIN (
    SELECT w.id,
        SUM(work.earnings) AS total_earnings,
        COUNT(work.order_id) AS assigned_jobs
    FROM users AS w
    LEFT JOIN (
        SELECT wr.order_id, wr.writer_id, o.fee AS earnings
        FROM work_records wr
        LEFT JOIN orders o ON o.order_id = wr.order_id
        WHERE wr.withdrawn IS NULL
          AND o.verified = 'verified'
        GROUP BY wr.order_id
    ) work ON work.writer_id = w.id
    GROUP BY work.writer_id
) te ON te.id = w.id

Collected from the Internet

Please contact [email protected] to delete if infringement.