ORACLE SQL Total Cost of a Customer-CodePudding

I have three related tables "customers", "orders", "orderdetails"

-"customers" has these columns:

CUSTOMERID, COMPANYNAME, CONTACTNAME, 
CONTACTTITLE, ADDRESS, CITY, REGION, 
POSTALCODE, COUNTRY, PHONE, FAX

-"orders" has these columns:

ORDERID, CUSTOMERID, EMPLOYEEID,
 TERRITORYID, ORDERDATE, REQUIREDDATE,
 SHIPPEDDATE, SHIPVIA, FREIGHT, SHIPNAME,
 SHIPADDRESS, SHIPCITY, SHIPREGION,
 SHIPPOSTALCODE, SHIPCOUNTRY

-"orderdetails" has these columns:

ORDERID, PRODUCTID, UNITPRICE, QUANTITY, DISCOUNT

I have to make a query where I get the total expense with this formula (unitpricequantity(1-discount) of a client called ASHRAM, that name is the abbreviation that appears in customerid

I have tried to do this:

SELECT customerid, COST (unitprice*quantity*(1-discount))
FROM CUSTOMERS JOIN orders ON customers.customerid=orders.customerid
JOIN orderdetails ON orders.orderid=orderdetails.orderid
WHERE customerid='ASHRAM';

But oracle tells me this: "column ambiguously defined"

CodePudding user response：

With some sample data:

SQL> with
  2  customers (customerid) as
  3    (select 'ASHRAM'     from dual union all
  4     select 'Littlefoot' from dual
  5    ),
  6  orders (orderid, customerid, orderdate) as
  7    (select 1, 'ASHRAM'    , date '2022-10-25' from dual union all
  8     select 2, 'ASHRAM'    , date '2022-11-13' from dual union all
  9     select 3, 'Littlefoot', date '2023-01-13' from dual
 10    ),
 11  orderdetails (orderid, productid, unitprice, quantity, discount) as
 12    (select 1, 123, 10, 5, 0.10 from dual union all
 13     select 1, 456, 20, 2, 0.00 from dual union all
 14     select 2, 999,  5, 5, 0.20 from dual union all
 15     select 3, 757, 50, 1, 0.00 from dual
 16    )

Query joins all 3 tables on appropriate columns, aggregates prices for all products per all orders for ASHRAM and groups result per customer ID:

 17  select c.customerid,
 18    sum(d.unitprice * d.quantity * (1 - d.discount)) total
 19  from customers c join orders o on o.customerid = c.customerid
 20                   join orderdetails d on d.orderid = o.orderid
 21  where c.customerid = 'ASHRAM'
 22  group by c.customerid;

CUSTOMERID      TOTAL
---------- ----------
ASHRAM            105

SQL>

CodePudding user response：

You need to define the tables by an alias if columns with the same name are existing in the query, otherwise oracle dont know witch column from which table you mean. This is also necessary on the where condition and on the select fields:


SELECT o.customerid, (s.unitprice * s.quantity * (1 - s.discount)) AS COST
FROM CUSTOMERS c JOIN orders o ON c.customerid = o.customerid
JOIN orderdetails s ON o.orderid = s.orderid
WHERE o.customerid='ASHRAM';