want to SUM two fields from different tables

[roachae]

I need to add two different fields in two separate tables, but the second sum is double 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 

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.

[smantscheff]

Add a CREATE TABLE and some INSERT statements for a complete test case for us.
Have a look at the result of your query. Which row appears more than once?
Change SELECT to SELECT DISTINCT. Does it change anything?

[roachae]

Adding a Distinct does nothing. What it looks like what I currently have (see my original post) is doing is multiplying the the second sum by the number of rows getting grouped. The first sum works great, it's just the second sum from the second table that the result is a multiple of what it should be. 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.

Add a CREATE TABLE and some INSERT statements for a complete test case for us.
Have a look at the result of your query. Which row appears more than once?
Change SELECT to SELECT DISTINCT. Does it change anything?

[smantscheff]

Submit the necessary CREATE TABLE statements and some data for a complete test case.

[roachae]

Best I can do for you is the sample data and table designs below. Sample results are what my query above spits out.

Table_A
-------
dc - varchar 255
rc - varchar 255
acctno - int 11
status - char 1

Table_A Sample Data
-------------------
dc | rc | acctno | status
-------------------------------
1234 | abcd | 123456 | A
2345 | bcde | 234567 | A
3456 | cdef | 345678 | C

Table_B
-------
acctno - int 11
ttc - varchar 10
ua - decimal 10 3

Table_B Sample Data
-------------------
acctno | ttc | ua
----------------------
123456 | X | 5.432
123456 | A | 2.345
123456 | B | 8.765
234567 | A | 1.234
234567 | X | 12.345
234567 | A | 9.876
345678 | B | 10.987
345678 | B | 2.345
345678 | B | 6.543
345678 | A | 0.123

Table_C
-------
acctno - int 11
cub - decimal 10 3

Table_C Sample Data
-------------------
acctno | cub
---------------
123456 | 10.987
123456 | 0.987
234567 | 6.543
234567 | 1.654
234567 | 0.789
345678 | 5.432

Query Result
------------
A.dc | A.rc | B.acctno | Total_UA | Total_CUB
---------------------------------------------
1234 | abcd | 123456 | 5.678 | 23.948
2345 | bcde | 234567 | 2.765 | 26.958

[smantscheff]

If this is the best you can do for me, what do you expect? You could have at least submitted instantly repeatable test code without need for re-formatting.

I don't even get the same test results:

drop table if exists table_A;
create table table_A (
dc  varchar (255),
rc  varchar (255),
acctno  int (11),
status  char (1)
);
insert into table_A values
('1234','abcd','123456','A'),
('2345','bcde','234567','A'),
('3456','cdef','345678','C');

drop table if exists table_B;
create table table_B (
acctno  int (11),
ttc  varchar (10),
ua  decimal (10, 3)
);

insert into table_B values
('123456','X','5.432'),
('123456','A','2.345'),
('123456','B','8.765'),
('234567','A','1.234'),
('234567','X','12.345'),
('234567','A','9.876'),
('345678','B','10.987'),
('345678','B','2.345'),
('345678','B','6.543'),
('345678','A','0.123');

drop table if exists table_C;
create table table_C (
acctno  int (11),
cub  decimal (10, 3)
);

insert into table_C values
('123456','10.987'),
('123456','0.987'),
('234567','6.543'),
('234567','1.654'),
('234567','0.789'),
('345678','5.432');

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 
;
+------+------+--------+----------+-----------+
| dc   | rc   | acctno | Total_UA | Total_CUB |
+------+------+--------+----------+-----------+
| 1234 | abcd | 123456 | 11.356   | 35.922    |
| 2345 | bcde | 234567 | -3.705   | 26.958    |
+------+------+--------+----------+-----------+

[roachae]

Give me a break, buddy. I wouldn't be here if my SQL knowledge was as almighty and powerful as your own. This is a forum to help people with less knowledge or to get help with issues, not a place to slam people that have limited resources and are only asking for help. The actual tables I'm using are already created, create statements you asked for are not around, and data within the tables are highly confidential, which is why I can only provide sample data. My knowledge of SQL is basically read/update, hence why I'm asking for help.

[roachae]

Further, even the values you got are wrong in the 2nd SUM(). They're a multiple of what the actual sum of the data should be. Which is my original problem in the first place. (Sum(C.cub)) As Total_CUB is not summing correctly.

[smantscheff]

If you strip your query of the group clause, you get the following "raw" data for the aggregation which show the problem: you have one row for each value of cub. I do not understand the semantics and therefore cannot delve deeper, but I think you have to work on the query below until it shows the correct results. Then make it a view and build your aggregate query on the view.

Select *,
If(B.ttc In ('X','Y','Z'), 0, B.ua) as s1, 
If(B.ttc In ('X','Y','Z'), B.ua, 0) as s2
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'
;
+--------+-----+--------+--------+--------+------+------+--------+--------+-------+--------+
| acctno | ttc | ua     | acctno | cub    | dc   | rc   | acctno | status | s1    | s2     |
+--------+-----+--------+--------+--------+------+------+--------+--------+-------+--------+
| 123456 | X   | 5.432  | 123456 | 10.987 | 1234 | abcd | 123456 | A      | 0     | 5.432  |
| 123456 | X   | 5.432  | 123456 | 0.987  | 1234 | abcd | 123456 | A      | 0     | 5.432  |
| 123456 | A   | 2.345  | 123456 | 10.987 | 1234 | abcd | 123456 | A      | 2.345 | 0      |
| 123456 | A   | 2.345  | 123456 | 0.987  | 1234 | abcd | 123456 | A      | 2.345 | 0      |
| 123456 | B   | 8.765  | 123456 | 10.987 | 1234 | abcd | 123456 | A      | 8.765 | 0      |
| 123456 | B   | 8.765  | 123456 | 0.987  | 1234 | abcd | 123456 | A      | 8.765 | 0      |
| 234567 | A   | 1.234  | 234567 | 6.543  | 2345 | bcde | 234567 | A      | 1.234 | 0      |
| 234567 | A   | 1.234  | 234567 | 1.654  | 2345 | bcde | 234567 | A      | 1.234 | 0      |
| 234567 | A   | 1.234  | 234567 | 0.789  | 2345 | bcde | 234567 | A      | 1.234 | 0      |
| 234567 | X   | 12.345 | 234567 | 6.543  | 2345 | bcde | 234567 | A      | 0     | 12.345 |
| 234567 | X   | 12.345 | 234567 | 1.654  | 2345 | bcde | 234567 | A      | 0     | 12.345 |
| 234567 | X   | 12.345 | 234567 | 0.789  | 2345 | bcde | 234567 | A      | 0     | 12.345 |
| 234567 | A   | 9.876  | 234567 | 6.543  | 2345 | bcde | 234567 | A      | 9.876 | 0      |
| 234567 | A   | 9.876  | 234567 | 1.654  | 2345 | bcde | 234567 | A      | 9.876 | 0      |
| 234567 | A   | 9.876  | 234567 | 0.789  | 2345 | bcde | 234567 | A      | 9.876 | 0      |
+--------+-----+--------+--------+--------+------+------+--------+--------+-------+--------+

[roachae]

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 anyone's 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

[smantscheff]

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 anyone's help.

I assume you are referring to financial transactions, not to database transactions. What are they, what is the separate field you are talking about, how do they have to be taken into consideration?

You can simplify and maybe accelerate your query like this:

Select A.dc,A.rc,A.acctno,
Sum(ua * (if(ttc In ('X','Y','C'),-1,1))) as Total_UA,C.Total_CUB From Table_A As A 
Join table_B 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(Total_UA - C.Total_CUB) > 0.0001
;

or even shorter

Select A.dc,A.rc,A.acctno,
Sum(ua * (if(ttc In ('X','Y','C'),-1,1))) as Total_UA,
(select sum(cub) from table_c C where c.acctno=a.acctno) as Total_CUB 
From Table_A As A 
Join table_B B on a.acctno=b.acctno
Group By A.acctno Having Abs(Total_UA - Total_CUB) > 0.0001
;

[roachae]

The separate field is self-descriptive ('Reverse'). And yes it is financial transactions. If an original transaction is to be reversed, the Reverse field is set to 'Y', and there is also a reversed transaction with Reverse set to 'Y'. If ttc is in ('X','Y','Z') and Reverse is 'Y' then it was a subtracted value that needs to be added back in. Basically if ttc is not in ('X','Y','Z') and Reverse is 'Y' then the value needs to be taken away.

[smantscheff]

Include that in the sum expression:
Sum(ua * (if(ttc In ('X','Y','C'),-1,1)) * if(Reverse = 'Y',-1,1)) as Total_UA