CODEWITHSUNDEEP
oracle-databaseselectsqlplus
cmorga1
cmorga1

Reputation: 91

Oracle sqlplus nested select's not working

I am trying to display the supplier code, supplier name and postcode of all suppliers who satisfy every stock item they supply having a price value above the average of all stock items.

I have been trying for a while, and this is the best I can come up with, although it isn't working

SELECT SuppName, SuppCode, Postcode
FROM Suppliers 
WHERE Suppliers.SuppCode = (
SELECT SuppCode
FROM Stocks 
WHERE Suppliers.SuppCode = Stocks.SuppCode
GROUP BY SuppCode
HAVING MIN(Price) > AVG(Price)
);

If anyone could give me a hint in the right direction as to where I am going wrong it would really be appreciated. 

   create table Stocks
  (StockNo      number(6) not null primary key,
   StoreCode    varchar2(6) not null, 
   Description  varchar2(24),
   Quantity     number(6),
   Units        varchar2(12),
   Reorder      number(6),
   foreign key (StoreCode)
     references Stores(StoreCode));

   create table Suppliers
  (SuppCode  varchar2(4) not null primary key,
   SuppName  varchar2(30),
   Street    varchar2(24),
   Town      varchar2(16),
   County    varchar2(16),
   PostCode  varchar2(10),
   TelNo     varchar2(16),
   FaxNo     varchar2(16))
  cluster SupplyData(SuppCode);

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

To get the codes for these supplies, I would go for aggregation. You seem to want:

SELECT s.SuppCode
FROM Stocks s
GROUP BY s.SuppCode
HAVING MIN(s.Price) > (SELECT AVG(s2.Price) FROM Stocks s2);

Upvotes: 1

Related Questions