Re: How add sums from different tables?

>From relay-wo!relay2!dcoxmac.nswc.navy.mil!user Fri Apr 2 09:03:05 EST 1993
 

>I need to get a total of sums from 4 different tables
>for a simple summary report I am creating via sqlplus.
>The field is "cost" in each case; in one case "cost" needs
>to be multiplied by another field "prtpairs."
 

>Is there a way to do this without storing intermediate
>sums in another table? This is an example of something
>which I expected to be relatively easy, but when it
>came to the nitty-gritty I realized I have not done
>something exactly like this before and can't figure it out.
 

>Thanks.

Dave,

     One way which will work is to use a view and the UNION operator. As an example, if you have 4 different pieces of the standard EMP table, called EMP1, EMP2, EMP3, and EMP4:

create view emp_all_sal as
select rowid,sal from emp1
  union
select rowid,sal from emp2
  union
select rowid,sal from emp3
  union
select rowid, sal from emp4;

Then,
select sum(sal) from emp_all_sal;

You have to use the rowid or you may end up with some duplicate rows which are then eliminated by the UNION. Also, I was able to make one of these something like sal*other_number in the view and still use sum(sal) for the final answer. I *did* have "sal" in the first select in the view so I guess that's what it defaults to calling the column - the documentation doesn't mention that point at all - if you use an expression in the first select in the view you have to use an alias.

Anyway, if what you need to do doesn't change, then this should work just fine - the view can be created once and then used over and over.

       Ruth Received on Fri Apr 02 1993 - 16:18:43 CEST