How to sum multiple columns of data from multiple tables in mysql?

Question

I have 4 tables:

1-Supplier

ID_A	Supplier name
1	apple
2	Millet
3	Nokia
4	OPPO

2-Start Balance

ID_B	Starting balance
1	1000
2	1000
3	1000
4	null

3- Invoice

ID_C	Invoice value
1	200
1	500
2	800
3	250
3	400
4	null

4-Return

ID_D	Return_value
1	100
2	50
2	25
3	null
4	null

Arithmetic method for displaying data as a result:

Starting Balance Invoice - Return = Final Balance

I tried using UNION and JOINS in mysql:

SELECT   null  , Supplier_name , ID_A  , SUM(Invoice_value) , null ,  null FROM Suppliers          
             inner  JOIN  Invoices 
             ON ID_A = ID_C 
group by ID_A  
        
 UNION ALL
 
 SELECT   null  , Supplier_name , ID_A  , null , SUM(Return_value),  null  FROM Suppliers          
             left  JOIN  Returns 
             ON ID_A = ID_D
             
group by ID_A 



UNION ALL

  SELECT   Start Balance ,  Supplier_name, ID_A   , null  , null   ,( Start Balance + ifnull(SUM(Invoice_value),0) - ifnull(SUM(Return_value),0) )  FROM Suppliers         
          left  JOIN   Start Balance 
           ON ID_A = ID_B
           left  JOIN  Invoices 
           ON ID_A = ID_C 
           left  JOIN  Returns 
           ON ID_A = ID_D 
          
           group by ID_A

I expect this result to be:

Starting balance	Supplier name	ID_A	Invoice value	Return_value	End_Balance
1000	apple	1	700	100	1600
1000	Millet	2	800	75	1725
1000	Nokia	3	650	null	1650
null	OPPO	4	null	null	null

But it doesn't work well, it shows the results in different rows and the calculation of the final balance is wrong What is the correct code to display this result?

Answer 1

You can sum the sums in subqueries and concatenate them together

SELECT   `Start Balance`,
      Supplier_name
    , ID_A   , `Invoice_value`
    , `Return_value`   ,
    `Start Balance` + IFNULL(Invoice_value,0) - ifnull(Return_value,0)  total  
   FROM Suppliers         
          left  JOIN   Start_Balance 
           ON ID_A = ID_B
           left  JOIN  
    ( SELECT `ID_C`, SUM(`Invoice_value`) `Invoice_value` FROM Invoices GROUP By ID_C)  i
           ON ID_A = ID_C 
           left  JOIN  
    ( SELECT `ID_D`, SUM(`Return_value`) `Return_value` FROM Returns GROUP BY `ID_D`) r
           ON ID_A = ID_D

Starting balance	Supplier name	ID_A	Invoice_value	Return_value	total
1000	apple	1	700	100	1600
1000	Millet	2	800	75	1725
1000	Nokia	3	650	null	1650
null	OPPO	4	null	null	null

violin