Re: SQL Question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/16
Message-ID: <34c0bfe0.244454657@inet16>#1/1

In a word -- cartesian product is the cause. Working with your data...

SQL>
SQL> select * from contracts;

CONTRACT_ID AMMEND_ID PARTICIPANTS
----------- ---------- ------------

          1          1           50
          1          2           50
          2          1           50

SQL> select * from commitments;

COMMITMENT_ID CONTRACT_ID AMMEND_ID VALUE ------------- ----------- ---------- ----------

            1           1          1        501
            2           1          1         25
            3           1          2         75
            4           1          2         30

Now, if we just join -- no aggregate, just join, we get:

SQL>
SQL> SELECT contracts.*, commitment_id, value   2 FROM CONTRACTS, COMMITMENTS
  3 WHERE COMMITMENTS.CONTRACT_ID(+) = CONTRACTS.CONTRACT_ID   4 AND COMMITMENTS.AMMEND_ID(+) = CONTRACTS.AMMEND_ID   5 /

CONTRACT_ID AMMEND_ID PARTICIPANTS COMMITMENT_ID VALUE ----------- ---------- ------------ ------------- ----------

          1          1           50             1        501
          1          1           50             2         25
          1          2           50             3         75
          1          2           50             4         30
          2          1           50

So, for each row in commitments that matches a row in contracts, the participants column is 'repeated'. So for contract_id,ammend_id(1,1) we have 2 rows in commitments that match -- so we get their values AND

What we really want to have is 2 tables such that both have a primary key of contract_id, ammend_id so when they are joined, each contributes AT MOST 1 row for each contract_id, ammend_id. Your problem is that for every row commitments contributes -- your participant count will be multiplied that number of times....

So your query:

SQL>
SQL> SELECT
  2 CONTRACTS.CONTRACT_ID,
  3 SUM(CONTRACTS.PARTICIPANTS) TOTAL_PARTICIPANTS,   4 SUM(COMMITMENTS.VALUE) TOTAL_DOLLAR_VALUE   5 FROM CONTRACTS, COMMITMENTS
  6 WHERE COMMITMENTS.CONTRACT_ID(+) = CONTRACTS.CONTRACT_ID   7 AND COMMITMENTS.AMMEND_ID(+) = CONTRACTS.AMMEND_ID   8 GROUP BY CONTRACTS.CONTRACT_ID
  9 /

CONTRACT_ID TOTAL_PARTICIPANTS TOTAL_DOLLAR_VALUE

----------- ------------------ ------------------
          1                200                631
          2                 50

Gives the right answer given the question asked (that should be apparent after seeing the result set BEFORE the group by as we did above).... Now for the query that fixes it. If you remember I said we want 2 tables that have the same primary key. The following query does that. Contracts is OK already -- its primary key is correct. The inline view below creates a virtual table whose primary key is now correct. We join to that and we are done...

SQL>
SQL> select a.contract_id, sum(participants), sum(value)   2 from contracts a,

  3         ( select contract_id, ammend_id, sum(value) value
  4             from commitments
  5                group by contract_id, ammend_id ) b
  6   where a.contract_id = b.contract_id(+)
  7     and a.ammend_id = b.ammend_id(+)

  8 group by a.contract_id
  9 /

CONTRACT_ID SUM(PARTICIPANTS) SUM(VALUE)

----------- ----------------- ----------
          1               100        631
          2                50

SQL>

SQL>
SQL> quit

On 15 Jan 1998 23:19:07 GMT, "Brad Worsfold" <bworsfold_at_vic.lgs.ca> wrote:

>Hello all.
>
>I have an intersting SQL question.
>
> Suppose I have two tables with datasets, the # below mark the primary key.
> The first tableis the parent, and the second table is the child.
[snip]

>I can get around this by creating two views of the tables and then doing a
>select from these views. It works fine, however, why does the query alone
>not work??? Notice that the TOTAL_DOLLAR_VALUE is correct, but not the
>TOTAL_PARTICIPANTS value.
>
>Any explanation/alternate solution would be nice.
>
>Thanks in advance!
>
>Brad
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jan 16 1998 - 00:00:00 CST