CODEWITHSUNDEEP
sql-serverdatabaset-sql
Boy
Boy

Reputation: 435

Join and sum multiple columns

Table TB1:

| PaymentID | CashAmount |
--------------------------
|     P1    |       3,000|
|     P2    |       5,000|
|     P3    |       8,000|

Table TB2:

|ChequeID| PaymentID |ChequeAmount|
-----------------------------------
|   C1   |     P2    |      10,000|
|   C2   |     P1    |      15,000|
|   C3   |     P1    |       2,000|

Table TB3:

|TransferID| PaymentID |TransferAmount|
---------------------------------------
|    T1    |     P2    |        20,000|
|    T2    |     P2    |        20,000|
|    T3    |     P1    |         3,000|

Expected Result

| PaymentID | CashAmount |ChequeAmount|TransferAmount|
------------------------------------------------------
|     P1    |       3,000|      17,000|         3,000|
|     P2    |       5,000|      10,000|        40,000|
|     P3    |       8,000|        NULL|          NULL|

How to write a query JOIN and SUM all these tables together? I tried writing simple join query but the result was wrong.

Example of incorrect query:

SELECT    
    TB1.PaymentID, TB1.CashAmount,
    SUM(TB2.ChequeAmount) AS ChequeAmount, 
    SUM(TB3.TransferAmount) AS TransferAmount
FROM        
    TB1
LEFT JOIN 
   TB2 ON TB1.PaymentID = TB2.PaymentID
LEFT JOIN 
   TB3 ON TB1.PaymentID = TB3.PaymentID
GROUP BY    
   TB1.PaymentID, TB1.CashAmount

Incorrect result:

| PaymentID | CashAmount | ChequeAmount | TransferAmount |
----------------------------------------------------------
|     P1    |      3,000 |      17,000  |         6,000  |
|     P2    |      5,000 |      20,000  |        40,000  |
|     P3    |      8,000 |        NULL  |          NULL  |

Upvotes: 2

Views: 3546

Answers (3)

automatic
automatic

Reputation: 2737

I would eliminate the duplicates in the minor tables first, then (left) join and sum with the major table, like this

SELECT      
  TB1.PaymentID, 
  TB1.CashAmount, 
  Sum(TB2.ChequeAmount) as ChequeAmount, 
  Sum(TB3.TransferAmount) as TranAmt
from TB1 
left join (
  select PaymentID, SUM(ChequeAmount) AS ChequeAmount
  from TB2 group by PaymentID
) TB2 on TB1.PaymentID = TB2.PaymentID
left join (
  select PaymentID, SUM(TB3.TransferAmount) AS TransferAmount
  from TB3 group by PaymentID
) TB3 on isnull(TB1.PaymentID, TB2.PaymentID) = TB3.PaymentID
group by
tb1.PaymentId, tb1.CashAmount

Upvotes: 2

Cetin Basoz
Cetin Basoz

Reputation: 23837

WITH  t2
        AS (
             SELECT [PaymentId], SUM([ChequeAmount]) AS ChequeAmount
             FROM   TB2
             GROUP BY [PaymentId]
           ),
      t3
        AS (
             SELECT [PaymentId], SUM([TransferAmount]) AS TransferAmount
             FROM   TB3
             GROUP BY [PaymentId]
           )
  SELECT  t.PaymentId, SUM(CashAmount) AS CashAmount,
          SUM(t2.[ChequeAmount]) AS [ChequeAmount],
          SUM(t3.[TransferAmount]) AS [TransferAmount]
  FROM    TB1 AS [t]
  LEFT JOIN [t2] ON [t2].[PaymentId] = [t].[PaymentId]
  LEFT JOIN [t3] ON [t3].[PaymentId] = [t].[PaymentId]
  GROUP BY [t].[PaymentId];

Upvotes: 0

James Z
James Z

Reputation: 12317

The problem you have is you're joining the tables that have multiple rows with the same id and it multiplies the numbers. You'll need to do the sum first and then join the data. Assuming the data can be missing from any of the tables, you'll also need to use full outer join. If TB1 has the row always, then left outer join is enough.

This is how you can do it:

SELECT      
  coalesce(TB1.PaymentID,TB2.PaymentID,TB3.PaymentID), 
  TB1.CashAmount, TB2.ChequeAmount, TB3.TransferAmount
from (
  select PaymentID, SUM(CashAmount) AS CashAmount
  from TB1 group by PaymentID
) TB1
full outer join (
  select PaymentID, SUM(ChequeAmount) AS ChequeAmount
  from TB2 group by PaymentID
) TB2 on TB1.PaymentID = TB2.PaymentID
full outer join (
  select PaymentID, SUM(TransferAmount) AS TransferAmount
  from TB3 group by PaymentID
) TB3 on isnull(TB1.PaymentID, TB2.PaymentID) = TB3.PaymentID

Example in SQL Fiddle

Upvotes: 5

Related Questions