Solved

MySQL - want to SUM two fields from different tables

Posted on 2011-02-25
5
236 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 39

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

756 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