Mysql Join 3 Tables and get SUM-CodePudding

I have 3 tables in my database. When i tried to join and sum two of the fields i am getting an error.

Customers

What i tried is

SELECT b.id,SUM(s.advance) as advance, SUM(c.qty) as sales 
FROM advance s
    INNER JOIN customers b ON s.cust_id = b.id
    INNER JOIN sales c ON b.id  = c.cust_id 
GROUP BY s.cust_id**

My output is

The o/p i am getting is wrong when there is more than one entry for the key in sales table, The expected o/p was

id  advance   sales
2   500       .5
1   500       .25
3   250       .75

I Checked these pages still i am getting the wrong output. Any help to resolve the issue is greatly appreciated.

MySQL JOIN with SUM and 3 tables

Joining three tables using MySQL

Joining 3 tables using mysql Mysql query to join three tables

CodePudding user response：

I make this example for your reference. Please check that out.

I use this query to get your described output (given above).

SELECT custID, advance, sales FROM (
SELECT id AS custID FROM ac) c
JOIN (
SELECT SUM(advance) AS advance, cust_id FROM aa GROUP BY cust_id) a ON c.custID = a.cust_id 
JOIN (
SELECT SUM(qty) AS sales, cust_id FROM `as` GROUP BY cust_id) s ON c.custID = s.cust_id ;