Join two tables and sum two columns from different table

Issue

SELECT m.*, 
       ms.s_mechanic_id, 
       sum(ms.s_qr_points) as total_points, 
       pm.p_mechanic_id, 
       sum(pm.paid_amount) as total_paid 
FROM mechanics m 
INNER JOIN pay_mechanic pm ON pm.p_mechanic_id = m.mechanic_id 
INNER JOIN mechanic_scanned ms ON ms.s_mechanic_id = m.mechanic_id 
GROUP BY m.mechanic_id

This is the query that I have been using but from both columns, I only get one column sum correct.

How can i get both sums correct?

Solution

Maybe you need to add an AS to the pay_mechanic and mechanic_scanned.

I’m working a lab app right now so I quickly threw this nonsensical query together, and it worked.

SELECT SUM(`Volume`),c.Number,SUM(c.Number) AS SUM2 FROM `Patient` JOIN `Client` AS c ON Number =`Client` WHERE `Patient` > 240590

Where Clientand Patient are columns in the Patient table.
So maybe something like:

INNER JOIN pay_mechanic as pm ON pm.p_mechanic_id = m.mechanic_id 

I don’t do joins very often… so good luck.

Answered By – Misunderstood

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published