CODEWITHSUNDEEP
sqloracle-database
Rayman
Rayman

Reputation: 23

SQL ORACLE Average Price

I have three tables "suppliers" "products" "categories"

-The "suppliers" table contains

SUPPLIERID, COMPANYNAME, CONTACTNAME,
 CONTACTTITLE, ADDRESS, CITY, REGION,
 POSTALCODE, COUNTRY, PHONE, FAX, HOMEPAGE

-The "products" table contains

PRODUCTID, PRODUCTNAME, SUPPLIERID,
 CATEGORYID, QUANTITYPERUNIT,
 UNITPRICE, UNITSINSTOCK, UNITSONORDER, 
REORDERLEVEL, DISCONTINUED

-The "categories" table contains

CATEGORYID, CATEGORYNAME, DESCRIPTION, PICTURE

I need to obtain a list of names of countries that supply products in the 'Seafood' category. Show only those with the average price greater than 10

I try this:

SELECT  country 
FROM suppliers JOIN products USING (supplierid) JOIN CATEGORIES  USING (categoryid)
WHERE LOWER (categoryname)='seafood' AND HAVING  AVG(unitprice)>10 ;

Upvotes: 0

Views: 39

Answers (1)

MT0
MT0

Reputation: 167982

You are missing a GROUP BY clause and do not want the AND before the HAVING:

SELECT  country 
FROM    suppliers
        JOIN products USING (supplierid)
        JOIN CATEGORIES  USING (categoryid)
WHERE   LOWER (categoryname)='seafood'
GROUP BY country
HAVING  AVG(unitprice)>10;

Upvotes: 2

Related Questions