Question 20400 in Mysql Question posted in
The official MySQL documentation can be found here.

How Can SUM data in Multi columns from multi tables in mysql?

I have 4 Tables :

1- Suppliers

ID_A Supplier_name
1 Apple
2 Xiaomi
3 Nokia
4 Oppo

2- Start Balance

ID_B Start Balance
1 1000
2 1000
3 1000
4 Null

3- Invoices

ID_C Invoice_value
1 200
1 500
2 800
3 250
3 400
4 Null

4- Returns

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

arithmetic method for the results to show data :

Start Balance + Invoices – Returns = End Balance

i tried to use UNION with 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 :

Start Balance Supplier_name ID_A Invoice_value Return_value End_Balance
1000 Apple 1 700 100 1600
1000 Xiaomi 2 800 75 1725
1000 Nokia 3 650 null 1650
null Oppo 4 null null null

but it didnt work well it show the results in differant rows and the calculate of end balance is wrong
please what is the wright code to show this result

Tags:

2

Answers


  1. 0

    You can sum the sums in subquery and join 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
    Start Balance Supplier_name ID_A Invoice_value Return_value total
    1000 Apple 1 700 100 1600
    1000 Xiaomi 2 800 75 1725
    1000 Nokia 3 650 null 1650
    null Oppo 4 null null null

    fiddle

    Login or Signup to reply.
  2. 0

    I got another solution(Mysql) with CTE…

    with jp as
 (
     select sb.`Start Balance`,sp.Supplier_name,sp.ID_A,sum(iv.Invoice_value) ivvle
     from Start_Balance sb
     join Suppliers sp on sp.ID_A = sb.ID_B
     join Invoices iv on  iv.ID_C = sb.ID_B
     group by 1,2,3
 ) 
     select jp.`Start Balance`,jp.Supplier_name,jp.ID_A,jp.ivvle Invoice_Valuee ,
     sum(Return_value) Return_Valuee,
     jp.`Start Balance`+jp.ivvle-ifnull(sum(Return_value),0) End_Balances
     from Returns rt 
     left join jp on rt.ID_D = jp.ID_A
     group by 1,2,3,4

    Result

    dbfiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search