Re: Calculations in SQL

In article <CGC6J7.GJp_at_lut.ac.uk> M.A.Hearnden_at_lut.ac.uk. (MAHearnden) writes:
>The tables are created in such a way that the total 'connects' in table1
>should equal to the total 'irequests' in table2.
>
>I want to check that this is so while the tables are being updated.
>The speed of Oracle is such (!) that if I do this in two
>separate sql statements, they won't be simultaneous.
>So I tried the statement
>select sum(connects),sum(irequests) from table1,table2
 ...
>The only thought that crossed my mind was that SQL had
>done a join of some kind: but why?
>
>Mavis
>
Because you told it to :-).

I you just want know if they're equal, try:

select count(*) from dual
where (select sum(connects) from table1) = (select sum(irequests) from table2)

That should(!) return 1 if they're equal, 0 if not. (I think it may return an error with the subquery as the LHS of the eqn.

If that doesn't work, try:

select sum(connects) from table1
having sum(connects) = (select sum(irequests) from table2)

that will return the sum if they're equal, no rows if not.

Hope that's enough to get you where you're going. L. Scott Johnson Received on Sat Nov 13 1993 - 03:08:30 CET