OraFAQ Forum: SQL & PL/SQL » Oracle Aggregation Problem in join between tables

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Aggregation Problem in join between tables (Oracle 9i,Windows 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle Aggregation Problem in join between tables [message #603763]

Thu, 19 December 2013 03:56

rajamohankumar
Messages: 26
Registered: July 2011
Location: TAMIL NADU

Junior Member

Dear Friends and Experts,

I have a problem of aggregating and getting correct data from two tables in oracle.I have two tables one is PRODUCT(master table) and another one is PRODUCT_DETAILS(detail table).The table structure of both tables and data has been shown like below,

TABLE 1 :PRODUCT

PRODUCT_ID, PRODUCT, PRICE

100, LAPTOP, 50000
200, MONITOR, 10000

TABLE 2 :PRODUCT_DETAILS

PRODUCT_ID, PRODUCT_NAME, PLACE_OF_PURCHASE

100, SONY, CHENNAI
100, HP, BANGALORE
200, SAMSUNG, CHENNAI
200, DELL , CHENNAI

Now I am trying to write a query to get the sum of price grouping by PRODUCT_ID of master table PRODUCT.The query I used below is,

SELECT
A.PRODUCT_ID,
SUM(A.PRICE)
FROM
HR.PRODUCT A,
HR.PRODUCT_DETAILS B
WHERE B.PRODUCT_ID=A.PRODUCT_ID
GROUP BY A.PRODUCT_ID;

And my result is,

PRODUCT_ID SUM(PRICE)
100 100000
200 20000

The above result is wrong because the Cartesian join and calculation occurs when I joined both the tables and sum the PRICE column.The query aggregate each and every row of detail table and give the result.

But the correct result is,

PRODUCT_ID SUM(PRICE)
100 50000
200 10000

How to solve this problem.
Please help me how to modify my query and get the result correctly.

Thanks.

Report message to a moderator

Re: Oracle Aggregation Problem in join between tables [message #603764 is a reply to message #603763]

Thu, 19 December 2013 04:07

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Why the join? Apparently you're doing nothing with the second table. Furthermore, if product_id is the primary key of table PRODUCT you don't want to aggregate. I've got the feeling something is missing from your story.

MHE

Report message to a moderator

Re: Oracle Aggregation Problem in join between tables [message #603765 is a reply to message #603764]

Thu, 19 December 2013 04:11

rajamohankumar
Messages: 26
Registered: July 2011
Location: TAMIL NADU

Junior Member

Thanks for your reply.But I want to check the product_id of 100 and 200 should be exists in the second table PRODUCT_DETAILS for some reason.I want join both these table and want to get the correct result.Its not my real scenario and tables,But I just posted to understand you all.Now please help me now to do this.
Thanks

Report message to a moderator

Re: Oracle Aggregation Problem in join between tables [message #603766 is a reply to message #603765]

Thu, 19 December 2013 04:24

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

So use EXISTS or IN to check the product exists in product details.

Report message to a moderator

Re: Oracle Aggregation Problem in join between tables [message #603768 is a reply to message #603766]

Thu, 19 December 2013 04:33

rajamohankumar
Messages: 26
Registered: July 2011
Location: TAMIL NADU

Junior Member

Yes.Its working with EXISTS AND IN.Thanks.Here my example I used two tables,but if this problem occurs between some 20 tables,still you want me to go for EXISTS AND IN?.How chasm traps and fan traps will be solved in sql query itself?

Report message to a moderator

Re: Oracle Aggregation Problem in join between tables [message #603771 is a reply to message #603768]

Thu, 19 December 2013 04:49

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

If you've got a query that requires 20 INs then I would personally assume there's something wrong with your table design.

Report message to a moderator

Re: Oracle Aggregation Problem in join between tables [message #603774 is a reply to message #603771]

Thu, 19 December 2013 05:08

rajamohankumar
Messages: 26
Registered: July 2011
Location: TAMIL NADU

Junior Member

Am talking about the situation which usually occurs in large applications like ERP,Data warehousing.There might be more tables than normal database applications.In such cases what would be the solution to avoid chasm traps and fan traps or aggregation join problems.

Report message to a moderator

Re: Oracle Aggregation Problem in join between tables [message #603777 is a reply to message #603768]

Thu, 19 December 2013 05:34

flyboy
Messages: 1903
Registered: November 2006

Senior Member

You may still make the aggregation in the subquery, something like this (WITH clause is available from 9iR2, otherwise just put that subquery to the WHERE clause directly or make it a view):

WITH product_aggr AS ( SELECT A.PRODUCT_ID, SUM(A.PRICE) PRICE
                       FROM HR.PRODUCT A
                       GROUP BY A.PRODUCT_ID )
SELECT A.PRODUCT_ID, A.PRICE
FROM PRODUCT_AGGR A, HR.PRODUCT_DETAILS B
WHERE B.PRODUCT_ID=A.PRODUCT_ID
GROUP BY A.PRODUCT_ID, A.PRICE;

or, if PRODUCT.PRODUCT_ID is unique - and it should, otherwise there would be a real cartesian join for duplicates in PRODUCT - just get rid of SUM around PRICE and add it to the GROUP BY clause.

Report message to a moderator

Re: Oracle Aggregation Problem in join between tables [message #603778 is a reply to message #603771]

Thu, 19 December 2013 05:40

msol25
Messages: 396
Registered: June 2011

Senior Member

Hi Friend,

If you know initially you will take all columns from one particluar table then please use query like this :


SELECT 
A.PRODUCT_ID,
SUM(A.PRICE) 
FROM
PRODUCT A,
(select distinct product_id 
 from PRODUCT_DETAILS
) B
WHERE B.PRODUCT_ID=A.PRODUCT_ID 
GROUP BY A.PRODUCT_ID;

Report message to a moderator