[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 767

# Adding columns together from two different tables

Hello,

I have two tables(6 columns in table 1, 3 in table 2), 4 integer columns in the first, 1 integer column in the second.  I have two queries (for each table) that give me the sum of the integer columns based off of date and person.  I now just want to add all the sum integer columns together from both tables based off of date and person.

I've tried joins and others but the numbers always come up wrong.
``````here are the two queries I'm using:
Table 1:

select person, trunc(c_date, 'MONTH'), sum(atc), sum(ide), sum(tatim), sum(wrap)
from agdtime
group by person, trunc(c_date, 'Month')

table 2

select person, trunc(c_date, 'MONTH'), sum(ide)
from uittime
group by person, trunc(c_date, 'Month')
``````
0
amaher84
• 6
• 2
1 Solution

Commented:
assuming both tables have all the same people and months in them...

SELECT   a.person,
TRUNC(a.c_date, 'Month'),
SUM(a.atc),
SUM(a.ide) a_ide,
SUM(a.tatim),
SUM(a.wrap),
SUM(u.ide) u_ide
FROM   agdtime a, uittime u
WHERE   a.person = b.person AND TRUNC(a.c_date, 'Month') = TRUNC(u.c_date,'Month')
GROUP BY   person, TRUNC(c_date, 'Month')

if they might not have the same people and months,  will one of them always be a super-set of the other or might each table have people/months that the other does not?

also, what version of Oracle are you using?  It's always helpful to post to the zone you are using.  You can post in up to 3,  it not only gives the volunteers are little more information about what you are trying to do, it also gets your question more attention by being in multiple zones.

0

Commented:
oops,  change "b.person" to "u.person"

SELECT   a.person,
TRUNC(a.c_date, 'Month'),
SUM(a.atc),
SUM(a.ide) a_ide,
SUM(a.tatim),
SUM(a.wrap),
SUM(u.ide) u_ide
FROM   agdtime a, uittime u
WHERE   a.person = u.person AND TRUNC(a.c_date, 'Month') = TRUNC(u.c_date,'Month')
GROUP BY   person, TRUNC(c_date, 'Month')
0

Commented:
if you are in 9i or above and either table might have records the other does not then try this...
Don't use this if any of the assumptions above are not true.

SELECT   a.person,
TRUNC(a.c_date, 'Month'),
SUM(a.atc),
SUM(a.ide) a_ide,
SUM(a.tatim),
SUM(a.wrap),
SUM(u.ide) u_ide
FROM   agdtime a FULL OUTER JOIN uittime u
ON a.person = u.person AND TRUNC(a.c_date, 'Month') = TRUNC(u.c_date, 'Month')
GROUP BY   person, TRUNC(c_date, 'Month')
0

Author Commented:
I'm using ORACLE 10g and the columns I specified are not in both tables.  I'm updating the query now to see what the results are. My main objective is to add all those columns together, so:

SELECT   a.person,
TRUNC(a.c_date, 'Month'),(SUM(a.atc)+SUM(a.ide)+
SUM(a.tatim)+SUM(a.wrap)+SUM(u.ide)) as TOTAL
FROM   agdtime a FULL OUTER JOIN uittime u
ON a.person = u.person AND TRUNC(a.c_date, 'Month') = TRUNC(u.c_date, 'Month')
GROUP BY   person, TRUNC(c_date, 'Month')

I see you have the group by function 'person, and 'c_date' not defined to a table, that caused me an error so I assigned them to the 'agdtime a' table. Would that be the correct table to assign them to and would the above query work in your opinion?  I'm running the one above that and will submit results when it finishes.

thanks
0

Commented:
actually, with a full outer join, what would be better would be...

0

Commented:
hmm, couldn't post directly, had to attach
ee.txt
0

Author Commented:
Thanks again
0

Commented:
glad I could help
0

## Featured Post

• 6
• 2
Tackle projects and never again get stuck behind a technical roadblock.