Oracle sqlplus nested select's not working

Question

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);

Gordon Linoff · Accepted Answer · 2016-11-30 03:05:08Z

1

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);

answered Nov 30, 2016 at 3:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cmorga1 Over a year ago

Thanks for your answer, I had this at one point but was then struggling with obtaining the supplier name and postcode aswell because I can't group when using two tables. I Tried using it as a sub query but it returns multiple rows so I was a bit stumped and ended up trying other solutions. Sorry i'm still on a learning curve with oracle, am I missing something obvious?

Gordon Linoff Over a year ago

@cmorga1 . . . You can get the supplier name by using an explicit JOIN or IN operator.

Collectives™ on Stack Overflow

Oracle sqlplus nested select's not working

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related