Multiple Aggregation in Query

[CCSlice]

I have a query that performs multiple aggregations which are totally wrong.   What I need to do is relate accounts with balances to account information (ie. name, address, etc.).   Within its own table, the balances are fine, but when I perform an outer join and match only account #'s the quantities show the same value of -626,547,021???   I don't understand the significance of this number.

Query1.Account_Number,
XSUM(Query3.Account_Balance) as Account_Balance,

from
(select distinct
Table.Account_Number,
Table.Branch_Code,
Table.Client_Number,
(((((Table.Branch_Code || '-') || Table.Account_Number) || '-') || Table.Account_Type) || Table..Check_Digit)  as  AccountNum,
from
Namespace.Table.
where
(Table.Branch_Code between '000000001' and '0000008989') and
(Table2.Address_Type ="AA"))
)Query1
full outer join
(select
(((((Table3.Branch_Code || '-') || Table3.Account_Number) || '-') || Table3.Account_Type) || Table3.Check_Digit)  as  AccountNum,
Table3.Client_Number,
Table3.Branch_Code,
Table3.Account_Number,
Table3.Account_Type,
xsum(Query3.Account_Balance) for ((((((Table3.Branch_Code || '-') || Table3.Account_Numebr) || '-') || Table3.Account_Type) || '-') || Table3.Check_Digit)) as Account_Balance
from
       Namespace.Table3.
group by
             ((((((Table3.Branch_Code || '-') || Table3.Account_Number) || '-') || Table3.Account_Type) || '-') || Table3.Check_Digit),
              Table3.CLIENT_NUMBER,
              Table3.BRANCH_CODE,
              Table3.Account_NUMBER,
              Table3.Account_Type,
              Table3.Check_Digit
) Query3
on (Query1.AccountNum=Query2.AccountNum)


Thanks.






   

[TheCognosDave]

Hello my friend,

fun and games with SQL eh ?

The problem is this:

The numbers are changing because the first part of your query does not have a 1 to 1 row match for the second query, so when you join them, your XSUM() function has more account_balance values to add up, because of the multiplicity.  I would predict that if you run the following block of SQL and look closely, you'll find that "AccountNum" is repeating for some rows.

select distinct
Table.Account_Number,
Table.Branch_Code,
Table.Client_Number,
(((((Table.Branch_Code || '-') || Table.Account_Number) || '-') || Table.Account_Type) || Table..Check_Digit)  as  AccountNum,
from
Namespace.Table.
where
(Table.Branch_Code between '000000001' and '0000008989') and
(Table2.Address_Type ="AA"))


If you look closely at this SQL and get the same multiplicity in both the first query and the second (assumably 1 row per AccountNum), I think you'll be much happier.

btw ... use INNER JOIN instead of FULL OUTER JOIN  ;)

good luck!

[CCSlice]

CognosDave,
Thanks for the tip!   In the back of my mind, I thought this was the case.  I think it would be better if I execute the query by aggregating all accounts THEN perform the join.

CCSlice
Fun and games in SQL... is not a gold medal!  But the boss will you give a shining star!  ;D

[TheCognosDave]

I'm happy to help buddy !  ;)