I am attempting to extract data from two tables and merge it into one table.
A bit of background - the tables are "new_txns" and "current_txns" in an accounting application. A transaction is in the "new" table until it is posted, and then moves to the "current" table. Both tables have identical column structure. There can be multiple entries for an account in each table. The data is only moved from new to current after the transactions have been posted. If only one table, I would do a group by and I would have no problem.
However, I need to get the sum of the amount field for a specific account from each table and then sum that total for that account (whew!). A very simple example would have an account 1234 with entries in both tables:
(NEW)
account amount
1234 123.40
1234 90.00
(CURRENT)
account amount
1234 45.00
Is this a two step operation, or is there some type of join that will allow me to handle this situation? Most attempts I have made have yielded a non-unique column situation.
Thanks in advance.
A bit of background - the tables are "new_txns" and "current_txns" in an accounting application. A transaction is in the "new" table until it is posted, and then moves to the "current" table. Both tables have identical column structure. There can be multiple entries for an account in each table. The data is only moved from new to current after the transactions have been posted. If only one table, I would do a group by and I would have no problem.
However, I need to get the sum of the amount field for a specific account from each table and then sum that total for that account (whew!). A very simple example would have an account 1234 with entries in both tables:
(NEW)
account amount
1234 123.40
1234 90.00
(CURRENT)
account amount
1234 45.00
Is this a two step operation, or is there some type of join that will allow me to handle this situation? Most attempts I have made have yielded a non-unique column situation.
Thanks in advance.
