-1

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

enter image description here

Customers

Advance

Sales

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

o/p

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

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
Rahul R
  • 303
  • 2
  • 11
  • 1
    first sum the sales table and then join it and please read https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question – nbk Mar 31 '22 at 17:07
  • i tried many ways but problem exists could you suggest me a soln – Rahul R Mar 31 '22 at 17:38
  • then you didn't read the multiple threads to this, your query must be like https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=e384e4a68292105ca4b7ca2fe6357cd7 – nbk Mar 31 '22 at 17:48

1 Answers1

1

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 ;
fonz
  • 167
  • 8