MySQL - want to SUM two fields from different tables

I need to add two different fields in two separate tables, but the second sum is a multiple of what it should be. Here's what I'm using:

Select A.dc,A.rc,B.acctno,
((Sum(If(B.ttc In ('X','Y','Z'), 0, B.ua))) - 
(Sum(If(B.ttc In ('X','Y','Z'), B.ua, 0)))) As Total_UA, 
(Sum(C.cub)) As Total_CUB 
From Table_B As B 
Left Join Table_C As C On B.acctno = C.acctno 
Left Join Table_A As A On B.acctno = A.acctno 
Where A.status != 'C' 
Group By B.acctno Having Abs(Total_UA - Total_CUB) > 0.0001

Open in new window


Total_UA is coming out fine, but Total_CUB is double what it should be. Any help would be much appreciated as to what I'm doing wrong or if there is a better way of doing this. It seems like the result of Total_CUB is a multiple of what it should be by the number of rows in the grouping. If my understanding of MySQL was better, I might know how to get around this. This is a critical issue I'm trying to fix. This query is needed to vet important data.
abitatAsked:
Who is Participating?
 
ee_autoConnect With a Mentor Commented:
Question PAQ'd and stored in the solution database.
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
Sum adds across rows. If you just want to add two columns you can select a,a+b,b from table

0
 
abitatAuthor Commented:
the two columns are in 2 different tables, and I'm not adding A+B from the two different tables, I'm summing A from the first table and B from the 2nd table. Look at my query - it's basically:

SUM(B.ua) As Total_UA
SUM(C.cub As Total_CUB
From B
Left Join C

This is just a simplified version... see the actual query in my original post.
0
 
abitatAuthor Commented:
Figured it out, but it takes forever to run. I also missed a field I need to account for within my Sum within Table_B. Requires another If(). Basically how the Table_B calculation is working is it's summing the totals of buys and sells (if ttc is X, Y, or Z, it's a sell). But there are reverse transactions (noted in a separate field) that needs to be taken into consideration within that calculation. If there's any suggestions how I can include this, I'd appreciate the help.

Select A.dc,A.rc,B.acctno,B.Total_UA,C.Total_CUB From Table_A As A 
Join (Select acctno,
	Sum(If(ttc In ('X','Y','C'),0,ua)) - Sum(If(ttc In ('X','Y','Z'),ua,0)) As Total_UA 
	From Table_B 
	Group By acctno 
) As B On A.acctno = B.acctno 
Join (Select acctno,
	Sum(cub) As Total_CUB 
	From Table_C 
	Group By acctno 
) As C On A.acctno = C.acctno 
Group By A.acctno Having Abs(B.Total_UA - C.Total_CUB) > 0.0001 

Open in new window


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.