0

I am new to sql so sorry if the answer is obvious but i couldn't find it anywhere. So i want to select the CategoryName,Description and the average price of the products that are in the same category.Below is the picture of the 2 tables involved.The problem is the description i cant find a way to show it. (There are 8 categories and every category has 1 description)

This is the code I have made so far but it has the error:

SELECT  c.CategoryName,c.Description,avg(p.UnitPrice) 
FROM Categories AS c
INNER JOIN Products AS p ON c.CategoryID=p.CategoryID
GROUP BY c.CategoryName

The error:

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

Sorry for my bad english :/

Thom A
  • 88,727
  • 11
  • 45
  • 75
Konstantinos Vidakis
  • 21
  • 3
  • the error is telling you your exact problem. You have Description in the list next to select but you do not have it in your group by clause – Matt Nov 27 '17 at 18:04
  • I tried that too but because its ntext it says: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. – Konstantinos Vidakis Nov 27 '17 at 18:05
  • the error about ntext and things would mean you are not allowed to group by it and it is a different error. But solve it by casting the column in the `SELECT` and `GROUP BY` to a data type that you can aggregate such as `CAST(c.Description AS VARCHAR(???))` where ??? is an acceptable length or use `MAX`. – Matt Nov 27 '17 at 18:08
  • I tried that before but only put it on group by because thats how i saw it somewhere and didnt work but putting on select too it works! Thanks a lot Matt, sorry for asking sth obvious. – Konstantinos Vidakis Nov 27 '17 at 18:15
  • What happens if you do the averaging in a CTE, like this? `WITH CatAvgs as( SELECT CategoryID 'catid', avg(UnitPrice) 'avgprice' FROM Products GROUP BY CategoryID ) SELECT c.CategoryName, c.Description, a.avgprice FROM CatAvgs a INNER JOIN Categories c ON c.CategoryID = a.catid` – SQLCliff Nov 27 '17 at 18:34
  • Well that works too actually – Konstantinos Vidakis Nov 27 '17 at 18:38

1 Answers1

0

(n)text can't be used in aggregate or window functions. It's also been deprecated since SQL Server 2008 (if I recall correctly, possibly 2005). You should really be using (n)varchar(MAX).

If you really "have" to use (n)text then you'll need to do your aggregate first, and then retrieve the value of your (n)text column:

WITH Averages AS (
    SELECT p.CategoryID, avg(p.UnitPrice) AS AveragePrice
    FROM p.CategoryID
    GROUP BY p.CategoryID)
SELECT C.CategoryName, C.Description, A.AveragePrice
FROM Averages A
     JOIN Categories C ON A.CategoryID = C.CategoryID;

(Note, this is untested due to lack of DDL and Sample Data)

Thom A
  • 88,727
  • 11
  • 45
  • 75