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')

Open in new window

amaher84Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
hmm, couldn't post directly, had to attach
ee.txt
0
 
sdstuberCommented:
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
 
sdstuberCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
sdstuberCommented:
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
 
amaher84Author 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
 
sdstuberCommented:
actually, with a full outer join, what would be better would be...

 
0
 
amaher84Author Commented:
Thanks again
0
 
sdstuberCommented:
glad I could help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.