CODEWITHSUNDEEP
mysqlsqljoinsum
Carlo Uy Matiao
Carlo Uy Matiao

Reputation: 17

Query to sum two tables

Query to sum two tables

I have a transaction table and a payment table. I need to get the total amount both on Trans Table and Payment Table and it must be group by the TransID. I hope somebody can help me with the right sql statement for this.

I tried the sql below but the sum of the amount on Trans Table will be multiplied by the number of records of the same TransID on the Payment table.

-> Select Trans.TransID, sum(Trans.Amount), sum(Payment.Amount) 
   from Trans
   Left Join Payment on Payment.TransID = Trans.TransID

I also tried the sql below but the processing time is longer compared with the sql statement above.

-> Select TD.TransID, TD.Amt, PD.paid 
   from
   (
       Select Trans.TransID, Sum(Trans.Amount) AS Amt 
       from Trans Group By Trans.TransID
   ) AS TD 
   Left Join
   (
       Select Payment.TransID, sum(Payment.Amount) AS Paid 
       from payment 
       Group by Payment.TransID
   ) AS PD On PD.TransID = TD.TransID

Trans Table

TransID |   Amount   
T1      |   10    
T2      |   15    
T3      |   12    
T4      |   20    
T5      |   11    
T1      |   15    
T5      |   14


----------

Payment Table

TransID |   Amount    
T1      |   5    
T3      |   10          
T1      |   3    
T2      |   5    
T5      |   10


----------

The query result I need is supposed to be this way:

TransID  |  Amount  |   Paid    
T1       |  25      |   8    
T2       |  15      |   5    
T3       |  12      |   10    
T4       |  20      |   0   
T5       |  25      |   10

Upvotes: 2

Views: 101

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

In terms of processing time, the following may be fastest, if you have an index on `Payment(TransId, Amount)

SELECT t.TransId, t.Amount,
       (SELECT SUM(p.Amount)
        FROM Payment p
        WHERE p.TransId = t.TransId
       ) as Paid
FROM (SELECT t.TransID, SUM(t.Amount) AS Amount
      FROM Trans t
      GROUP BY t.TransID
     ) t;

MySQL will take advantage of an index for the correlated subquery, which should be faster than three group by queries.

Upvotes: 0

fthiella
fthiella

Reputation: 49089

You can use two different queries, one that sums all Amounts (and sets the Paid column to 0) and one that sums all Paid values (and sets the Amount column to 0) and combine using a UNION ALL query, and SUM the results:

SELECT TransID, SUM(Amount) AS Amount, SUM(Paid) AS Paid
FROM (
  SELECT TransID, SUM(Amount) AS Amount, 0 AS Paid
  FROM Trans
  GROUP BY TransID
  UNION ALL
  SELECT TransID, 0 AS Amount, SUM(Amount) AS Paid
  FROM Payment
  GROUP BY TransID
) s
GROUP BY TransID

Please see a fiddle here.

Upvotes: 1

Related Questions