A simple sql query problem

bennyineire

Hey guys and gals, just wondering if you can give me a hand with this query , it's driving me around the bend,

Query
Create a query to display the total sales price of all orders from each customer (two table involved)

here is my query code

SELECT lname,fname,cust_nbr,
SUM(sale_price)
From customer JOIN cust_order on cust_nbr=cust_nbr
GROUP BY lname;

I get this error, ORA-00918: column ambiguously defined

when I run these separate query's they work no bother

SELECT cust_nbr,SUM(sale_price)
FROM cust_order
GROUP BY cust_nbr;

SELECT lname,fname
FROM customer;

this has been driving me nuts for a couple of hours and I'm sure it's just something simple I'm missing in the join, any help would appreciated.
Regards from a noob

Aswerty

Try changing this line

From customer JOIN cust_order on cust_nbr=cust_nbr

to

From customer JOIN cust_order on customer.cust_nbr=cust_order.cust_nbr

Now you are specifying the table since the columns in both tables are the same name.

bennyineire

Thanks for the reply, tried that but I'm still getting the same error

jordata

Try changing your select clause to
SELECT lname,fname,customer.cust_nbr, SUM(sale_price)


As cust_nbr appears in both tables??

BrokenArrows

You need to name the tables you are using and place that name infront of the columns you want from that table.
It is required incase there are columns in both tables with the same name.

SELECT [B]cust.[/B]lname,[B]cust.[/B]fname,[B]cust_ord.[/B]cust_nbr,
 SUM([B]cust_ord.[/B]sale_price)
 From customer [B]cust[/B] 
      JOIN cust_order [B]cust_ord[/B] 
          on [B]cust.[/B]cust_nbr=[B]cust_ord.[/B]cust_nbr
 GROUP BY [B]cust.[/B]lname,[B]cust.[/B]fname,[B]cust_ord.[/B]cust_nbr;

Edit. Also you need to include all columns which are not summed in the group by.

bennyineire

Yep that sorted , thanks a bunch guys, I see where I was going wrong