CODEWITHSUNDEEP
mysqljoinleft-joinouter-join
Kaya Kipgen
Kaya Kipgen

Reputation: 21

Joining two table with SUM of Column and Group by another column

Need help to join and group with SUM() function two tables.
The Tables are as under:


DEBIT TABLE                 CREDIT TABLE                
---------------------       ---------------------
CustomerName    Debit       CustomerName    Credit
---------------------       ---------------------
Customer1       200.00      Customer1       100.00
Customer1       300.00      Customer1       100.00
Customer2       100.00      Customer1       100.00
Customer2       600.00      Customer2       200.00
-----------------------     Customer2       300.00
                            Customer2       50.00
                            -----------------------

I need to show the two Join table as:


JOIN TABLE(DEBIT CREDIT TABLE)              
------------------------------------------
CustomerName    Debit   Credit  Closing
------------------------------------------
Customer1       500.00  300.00  200.00
Customer2       700.00  550.00  150.00
------------------------------------------

This is as far as I am getting but doesn't yield the right result:



"SELECT Debit_Tbl.CustomerName, SUM(Debit_Tbl.Debit) as Debit, SUM(Credit_Tbl.Credit) as Credit, SUM(Debit_Tbl.Debit) - SUM(Credit_Tbl.Credit) as Closing from Debit_Tbl LEFT OUTER JOIN Credit_Tbl ON Debit_Tbl.CustomerName = Credit_Tbl.CustomerName GROUP BY Debit_Tbl.debit UNION SELECT Debit_Tbl.CustomerName, SUM(Debit_Tbl.Debit) as Debit, SUM(Credit_Tbl.Credit) as Credit, SUM(Debit_Tbl.Debit) - SUM(Credit_Tbl.Credit) as Closing from Debit RIGHT OUTER JOIN Credit_Tbl ON Debit_Tbl.CustomerName = Credit_Tbl.CustomerName GROUP BY Debit_Tbl.debit"
-------------------------------------------

Upvotes: 2

Views: 1213

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Your query is producing a cartesian product between the two tables. Instead, use union all to separate the debits and credits into separate columns -- without multiplying the number of rows. Then do the aggregation:

select customerName, sum(debit) as debit, sum(credit) as credit,
       sum(debit) - sum(credit) as closing
from (select d.customerName, debit as debit, 0 as credit
      from debit d
      union all
      select c.customerName, 0 as debit, credit
      from credit c
     ) cd
group by customerName;

Upvotes: 1

road242
road242

Reputation: 2532

This should work:

SELECT customerName,
  debit,
  credit,
  debit-credit AS closing
FROM
  (SELECT d.CustomerName AS customerName,
    SUM(d.debit)         AS debit,
    SUM(c.credit)        AS credit
  FROM debit d
  INNER JOIN credit c
  ON d.CustomerName = c.CustomerName
  )

Upvotes: 0

Related Questions