Re: Calculations in SQL

In article <CGC6J7.GJp_at_lut.ac.uk>, M.A.Hearnden_at_lut.ac.uk. (MAHearnden) writes:
>Newsgroups: comp.databases.oracle
>From: M.A.Hearnden_at_lut.ac.uk. (MAHearnden)
>Subject: Calculations in SQL
>Reply-To: M.A.Hearnden_at_lut.ac.uk. (MAHearnden)
>
>We're using RDBMS 6.0.36.7.1, on an HP-UX (not that I feel the
>platform is especially relevant).
>I've got two tables: table one has a number field called
>connects, table two has a number field called irequests.
>The tables don't have fields of the same name.
>
>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
>
>and got utterly different results: one result was 8 digits
>instead of 4, the other was 6 digits instead of 4.
>
>The only thought that crossed my mind was that SQL had
>done a join of some kind: but why?
>
>Mavis
>
>
>--
>Mavis Hearnden
>Computer Centre
>Loughborough University,
>Leicestershire
>

    My Introduction to RDBMS Part 1 Page 7-7 says :-

    Product :

         When a join condition is invalid or omitted completely,
         the result is a PRODUCT, and all combinations of rows will be
         displayed.

     Your select has joined EACH row of table 2 to EVERY row of table 1
     to give the unexpected result you obtained.

     Try :

     select 'Connects',  sum(connects)  from table1
     union
     select 'Irequests', sum(irequests) from table2;

 Ian

+--------------------------+-----------------------------+---------------------+
| Ian Bainbridge           |Working Philosophy:          |Sensible Advice :    |
| bainbridge_i_at_bgers.co.uk |                             |                     |
| British Gas ERS,         |Just trying to get to Friday |Don't Eat The Yellow |
| Newcastle Upon Tyne, UK  |with as little harrasment as |Snow  - Frank Zappa? |
| Phone: 091-216-0202      |possible                     |                     |
 +--------------------------+-----------------------------+---------------------+