Solved

MySQL - want to SUM two fields from different tables

Posted on 2011-02-25
5
234 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:abitat
  • 2
5 Comments
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 34979842
Sum adds across rows. If you just want to add two columns you can select a,a+b,b from table

0
 

Author Comment

by:abitat
ID: 34979929
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
 

Author Comment

by:abitat
ID: 34983197
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
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 37313422
Question PAQ'd and stored in the solution database.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question