Oracle how to get this result?

[cpu2007]

Hello everyone
I am trying to solve this problem which I can't find a solution of because I am new to Oracle and hope someone can guide me.

I have attached the .sql file which creates the tables and everything and from this database I need to:
Show the dlrcode-dlrname (which is the dealer code and dealer name)
I need to calculate the total number of bike sold to each dealer
and the total sales price to each dealer.

Now I managed to do a little but are not being able to do it all.
This is the query I made and if someone things there are amendmends to do please let me know.

<CODE>SELECT d.dlrname,b.DLRCODE, b.bikepurchprice, b.bikesellprice
FROM rr_bike b,rr_dealer d
where d.dlrname=d.dlrname and b.dlrcode>0;</CODE>

Thank you in advance.

[cpu2007]

any hsuggestion please/

[ideru]

I can't look at your sql but I assume that for each dealer code you can have only one dealer name right?

then use group by dealer code and compute what you need to compute
check the example here: http://www.plus2net.com/sql_tutorial/sql_sum.php

[jcaccia]

SELECT d.dlrname,b.DLRCODE, b.bikepurchprice, b.bikesellprice
FROM  rr_bike b,rr_dealer d
where d.dlrname=d.dlrname and b.dlrcode>0;

This query creates a cross product of the rr_bike records with a dlrcode > 0 and rr_dealer records with a non-null dlrname (d.dlrname is always equal to itself, except when it is null).

You have to change the WHERE clause to have a one-to-many relationship between the two tables and use grouping to calculate the totals you need.

As suggested by ideru, you may also have issues if different dealers can have the same name.