Can someone please help me to correct this SQL 08 query?

Question

When I run the below SQL 08 query I get an error that tbrm_Article.ArticleID is invalid because it is not contained in an aggregate function or GROUP BY clause.

I know the problem is related to the: AVG(tbrm_Votes.True) AS Truth, AVG(tbrm_Votes.False) AS False, and the subsequent join to the tbrm_Votes table.

the tbrm_votes table holds an article id, true number column, false number column. I am trying to average all of the true and false numbers for a given article id. The article id can appear multiple times in the votes table.

SELECT * FROM

(

SELECT
tbrm_Article.ArticleID, 
tbrm_Article.CountryID,
tbrm_Article.CategoryID,
tbrm_Article.Title,
tbrm_Article.ArticleDetail,
tbrm_Article.Source,
tbrm_Article.ArticleDateTimeAdded,
tbrm_Article.ViewCount,
tbrm_Article.CommentCount,
tbrm_CountryList.CountryName AS CountryName,
tbrm_CountryList.CountryImage AS CountryImage,
tbrm_CategoryList.CategoryName AS CategoryName,
tbrm_CategoryList.CategoryImage AS CategoryImage,
aspnet_Users.UserName AS UserName,
AVG(tbrm_Votes.True) AS Truth,
AVG(tbrm_Votes.False) AS False,
ROW_NUMBER() OVER (ORDER BY tbrm_Article.ArticleDateTimeAdded DESC) AS RowRank

FROM

tbrm_Article INNER JOIN
tbrm_CountryList ON tbrm_Article.CountryID = tbrm_CountryList.CountryID INNER JOIN
tbrm_CategoryList ON tbrm_Article.CategoryID = tbrm_CategoryList.CategoryID INNER JOIN
    aspnet_Users ON tbrm_Article.UserID = aspnet_Users.UserID INNER JOIN
    tbrm_Votes ON tbrm_Article.ArticleID = tbrm_Votes.ArticleID



)  Article
WHERE Article.RowRank > @PageIndex AND RowRank <= (@PageIndex + @PageSize)
ORDER BY Article.ArticleDateTimeAdded DESC

If anyone can show me where I am going wrong I would appreciate it. Thanks

Answer 1

Replace aggregates with analytics:

AVG(tbrm_Votes.True) OVER() AS Truth
AVG(tbrm_Votes.False) OVER() AS False

Answer 2

If you have a avg or other aggreagate functions you need to say how the other fields are combined.

ie over what rows the average is calculated.

This is done by the group by command. This says take these fields and do the summary over the rows having the same value in these fields. (Sybase document says 'group by is typically used in conjunction with aggregates to specify how to group the unaggregated columns of a select query.')

Thus here we need

group by
  tbrm_Article.ArticleID, 
  tbrm_Article.CountryID,
  tbrm_Article.CategoryID,
  tbrm_Article.Title,
  tbrm_Article.ArticleDetail,
  tbrm_Article.Source,
  tbrm_Article.ArticleDateTimeAdded,
  tbrm_Article.ViewCount,
  tbrm_Article.CommentCount,
  tbrm_CountryList.CountryName 
  tbrm_CountryList.CountryImage 
  tbrm_CategoryList.CategoryName 
  tbrm_CategoryList.CategoryImage 
  aspnet_Users.UserName AS UserName