How do I fix this SQL statement to AVG muliple rows from another table?

Question

I am having a few issues with the below SQL.

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) OVER() AS Truth,
               AVG(tbrm_Votes.False) OVER() AS False,
               ROW_NUMBER() OVER (ORDER BY tbrm_Article.ArticleDateTimeAdded DESC) AS RowRank      
          FROM tbrm_Article 
          JOIN tbrm_CountryList ON tbrm_Article.CountryID = tbrm_CountryList.CountryID 
          JOIN tbrm_CategoryList ON tbrm_Article.CategoryID = tbrm_CategoryList.CategoryID 
          JOIN aspnet_Users ON tbrm_Article.UserID = aspnet_Users.UserID 
          JOIN tbrm_Votes ON tbrm_Article.ArticleID = tbrm_Votes.ArticleID)  Article
   WHERE Article.RowRank > @PageIndex AND RowRank <= (@PageIndex + @PageSize)
ORDER BY Article.ArticleDateTimeAdded DESC

Everything works fine apart from the two AVG statements. Instead of averaging only one applicable relevant article id it is returning the average for the whole votes table of values. Any ideas of the best way to fix this? I am using SQL Server 08.

Answer 1

If you use an aggregate function, such as avg, you need to use partition by in your over clause, or include the columns selected that aren't aggregates in your group by clause.

Like so:

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

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


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

If you wanted it just by CountryName, for example, you would drop the group by clause and use:

avg(tbrm_Votes.True) over (partition by tbrm_CountryList.CountryName) as Truth

Answer 2

Cunners,

I think it will help if you explain what you want, especially with the paging part of the picture. If you just add a PARTITION BY clause to the AVG() expressions' OVER clauses, you'll still get a row for every single vote, and I doubt that's what you want.

If I had to guess, I'd guess you want something like this, but I don't know the cardinalities of your joins (or much else), so it's just a guess.

WITH ARTICLE AS (
  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,
  Truth,
  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 
  CROSS APPLY (
    SELECT tbrm_Votes.ArticleID, AVG(tbrm_Votes.True), AVG(tbrm_Votes.False)
    FROM tbrm_Votes
    WHERE tbrm_Article.ArticleID = Votes.ArticleID
    GROUP BY tbrm_Votes.ArticleID
  ) AS Votes(ArticleID,Truth,False)
)
  SELECT * FROM ARTICLE
  WHERE ARTICLE.RowRank > @PageIndex AND ARTICLE.RowRank <= (@PageIndex + @PageSize)
  ORDER BY ARTICLE.ArticleDateTimeAdded DESC