CODEWITHSUNDEEP
sqlsql-server
Jamie
Jamie

Reputation: 3071

SQL Server : cannot perform an aggregate function on an expression containing an aggregate or a subquery

I'm currently getting the error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery

The full query:

query = "SELECT * FROM (SELECT" +
" IDWebsite = Websites.Id, " +
" Websites.Title, " +
" Websites.Description, " +
" Websites.Url, " +
" Websites.BannerURL,  " +
" (Select Count(*) From Votes where WebsiteID = Websites.Id) as TotalVotes, " +
" AVG(ISNULL((Select Rating From WebsiteRating where WebsiteID = Websites.Id), 5)) as Rating, " +
" Users.Username, " +
" Websites.ID, " +
" (Select Count(*) From Redirects where WebsiteID = Websites.Id) as websiteCount, " +
" RowNum = ROW_NUMBER() OVER (ORDER BY Websites.ID) " +
" FROM Websites " +
" INNER JOIN Users ON Websites.UserID = Users.Id " +
" LEFT OUTER JOIN Votes AS Votes_1 ON Votes_1.WebsiteID = Websites.Id  " +
" LEFT OUTER JOIN Redirects AS Redirects_1 ON Redirects_1.WebsiteID = Websites.Id " +
" LEFT OUTER JOIN WebsiteRating AS WebsiteRating_1 ON WebsiteRating_1.WebsiteID = Websites.Id " +
" GROUP BY Websites.Title, Websites.Description, Websites.Url, Websites.BannerURL , Users.Username, Websites.ID" +
") as Table1 " +
"WHERE RowNum > " + number + " And RowNum <= " + amount + " " +
"Order by TotalVotes ";

The line causing the problem is:

" AVG(ISNULL((Select Rating From WebsiteRating where WebsiteID = Websites.Id), 5)) as Rating, " +

I tried to fix it myself with tutorials, but unfortunately I was getting various other errors. I'm still a beginner, so I don't have a lot of knowledge about SQL unfortunately..

If anyone could help me out, i would be very thankful!

Thanks in advance.

Upvotes: 0

Views: 1613

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

The problem is with your subqueries. It is quite difficult to read/modify a query that is presented as a long set of string concatenations. It is better to just show the query in the question.

Consider this subquery:

(Select Count(*) From Votes where WebsiteID = Websites.Id) as TotalVotes

The problem is that Websites.Id is not well defined by the outer query. You have three choices. First, you can change it to:

(Select Count(*) From Votes where WebsiteID = max(Websites.Id)) as TotalVotes

Or, you could include Websites.Id in the group by clause.

Or, you could actually do the comparison logic on the fields in the group by.

Upvotes: 1

Related Questions