Microsoft SQL Server Management Studio:

I've finished Queries 1-3, I just need help with the correct syntax for Query 4 as I am receiving the error:

"Column 'Suppliers.CompanyName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

I've included the schema and the query I built off of (that returned correct values) for reference.

-----------------------------------------------------------------------------------------------------------------

Query 3. Copy the statement of Query 2 and modify it to display SupplierID and Avg_UnitPrice of suppliers whose average unit price is ranked #1 and #2 (i.e., the highest two averages). Hint: 3 suppliers

Query 4. Copy the statement of Query 3 and modify it to display the same output plus two more columns: CompanyName and Phone of each included supplier. Hint: 3 suppliers, join Suppliers table

------------------------------------------------------------------------------------------------------------------

Your SELECT statement for Query 3:

SELECT TOP 3 SupplierID, AVG(UnitPrice) FROM Products GROUP BY SupplierID having AVG(UnitPrice) BETWEEN 15 and 22 order by AVG(UnitPrice) DESC;

Your SELECT statement for Query 4:

SELECT TOP 3 p.SupplierID, AVG(p.UnitPrice), s.CompanyName, s.Phone FROM Products p JOIN Suppliers s ON p.SupplierID = s.SupplierID GROUP BY p.SupplierID having AVG(p.UnitPrice) BETWEEN 15 and 22 order by AVG(p.UnitPrice) DESC;

O dbo.Categories o Columns CategoryID (PK, int, not null) CategoryName (nvarchar(15), not null) Description (nvarchar(max), null) Keys Constraints # Triggers Indexes Statistics dbo.Products Columns ProductID (PK, int, not null) ProductName (nvarchar(40), not null) - SupplierlD (FK, int, null) - CategoryID (FK, int, null) QuantityPerUnit (nvarchar(20), null) UnitPrice (money, null) UnitsInStock (smallint, null) UnitsOnOrder (smallint, null) ReorderLevel (smallint, null) Discontinued (bit, not null) Keys + Constraints # Triggers Indexes Statistics o dbo.Suppliers o Columns Supplier D (PK, int, not null) CompanyName (nvarchar(40), not null) ContactName (nvarchar(30), null) ContactTitle (nvarchar(30), null) Address (nvarchar(60), null) City (nvarchar(15), null) Region (nvarchar(15), null) Postal Code (nvarchar(10), null) Country (nvarchar(15), null) Phone (nvarchar(24), null) Fax (nvarchar(24), null) HomePage (nvarchar(max), null)