SQL Query Stored Procedure Errors

Question

I'm creating an e-commerce benchmark website. I have some problems in my GETSITES stored procedure:

Code:

CREATE PROC USER_S_GETSITES
   @CategoryID int,
   @List int,
   @IsPopular bit,
   @MaxID int,
   @Status bit,
   @Word text
AS
    SELECT s.*, c.*, AVG(r.Rate) AS RateAVG, Count(r.ID) AS RateCount 
    FROM Sites AS s
    INNER JOIN Rates AS r ON r.SiteID = s.ID
    INNER JOIN Categories AS c ON c.ID = s.CategoryID
    WHERE (@CategoryID is NULL) OR s.CategoryID = @CategoryID
      AND (@MaxID is NULL) OR s.ID < @MaxID
      AND (@Status is NULL) OR s.Status = @Status
      AND r.Status=1
      AND (@Word is NULL) OR s.Name LIKE @Word OR s.Description LIKE @Word
      AND (@IsPopular is NULL) OR s.IsPopular=@IsPopular 
ORDER BY
    CASE @List WHEN 1 THEN s.ID END ASC,
    CASE @List WHEN 2 THEN s.ID END DESC,
    CASE @List WHEN 3 THEN RateAVG END DESC,
    CASE @List WHEN 4 THEN RateCount END DESC

And my problems :

Msg 8120, Level 16, State 1, Procedure USER_S_GETSITES, Line 9
Column 'Sites.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 207, Level 16, State 1, Procedure USER_S_GETSITES, Line 23
Invalid column name 'RateAVG'.
Msg 207, Level 16, State 1, Procedure USER_S_GETSITES, Line 24
Invalid column name 'RateCount'.

I can't solve these problems. What should I do?

Columns without aggregate functions(SUM,COUNT etc) must be in the GROUP BY clause. — Mihai
– Mihai, Commented Jan 5, 2014 at 12:13
ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. See details here — marc_s
– marc_s, Commented Jan 5, 2014 at 12:16
Every non aggregate column must be mentioned in the group by clause, which I suspect means you are not going to get the numbers you want seeing as you are selecting every column in sites and categories. Usual way of doing this sort of thing. Is to have an aggregate query and group by it's unique keys and then join back to the original data by that / those keys. — Tony Hopkinson
– Tony Hopkinson, Commented Jan 5, 2014 at 12:30
I havent solve this problem yet 'cause i didnt understand. Can you write sample SQL code please? — Ali Naci Arbak
– Ali Naci Arbak, Commented Jan 5, 2014 at 16:06

Rahul · Accepted Answer · 2014-01-05 12:26:59Z

1

Error 1: include column Sites.ID in GROUP BY clause like group by Sites.ID, ....

Error 2: CASE @List WHEN 3 THEN AVG(r.Rate) AS RateAVG END DESC

Error 3: CASE @List WHEN 4 THEN Count(r.ID) AS RateCount END DESC

answered Jan 5, 2014 at 12:26

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

Collectives™ on Stack Overflow

SQL Query Stored Procedure Errors

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related