Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL - want to SUM two fields from different tables

Posted on 2011-02-25
5
Medium Priority
?
244 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

715 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