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

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

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

0
amaher84
Asked:
amaher84
  • 6
  • 2
1 Solution
 
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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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
 
sdstuberCommented:
hmm, couldn't post directly, had to attach
ee.txt
0
 
amaher84Author Commented:
Thanks again
0
 
sdstuberCommented:
glad I could help
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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