Sql error on a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sql error on a query

Author

Topic

spinoza
Starting Member

49 Posts

Posted - 2006-01-21 : 09:07:12

I am trying to execute the following query

SELECT count(ARTICLE_RATING.rating) as numberoftimes, ARTICLE_RATING.rating, sum(rating/numberoftimes) from ARTICLE_RATING
group BY ARTICLE_RATING.rating

and i receive the following error.

Invalid column name 'numberoftimes'...Could someone help me with this??

Kristen
Test

22859 Posts

Posted - 2006-01-21 : 09:48:26

You can't use a column from the resultset in your calculation.

SELECT count(ARTICLE_RATING.rating) as numberoftimes, ARTICLE_RATING.rating, sum(rating/numberoftimes) from ARTICLE_RATING
group BY ARTICLE_RATING.rating

Kristen

SamC
White Water Yakist

3467 Posts

Posted - 2006-01-21 : 10:42:52

Maybe:


SELECT count(ARTICLE_RATING.rating) as numberoftimes, ARTICLE_RATING.rating, sum(rating) / COUNT(ARTICLE_RATING.rating) As AverageRating
  from ARTICLE_RATING
  group BY ARTICLE_RATING.rating

spinoza
Starting Member

49 Posts

Posted - 2006-01-21 : 12:58:10

doesnt work... in general...how can one handle a problem like this

Kristen
Test

22859 Posts

Posted - 2006-01-21 : 13:24:32

"in general...how can one handle a problem like this"

Possibly by using a nested query:


SELECT rating,
       NumberOfTimes,
       (rating / NumberOfTimes) As AverageRating
FROM (
         SELECT AR.rating,
                COUNT(AR.rating) as NumberOfTimes
         FROM   dbo.ARTICLE_RATING AS AR
         GROUP BY AR.rating
     ) AS T

Kristen

SamC
White Water Yakist

3467 Posts

Posted - 2006-01-21 : 18:57:36

Wasn't paying attention. To return the average rating per article, you need to group on the Article, not the Rating.

Something like


SELECT ArticleID, AVE(Rating) As AverageRating, COUNT(*) As NumberOfRatings
  FROM MyTable
  GROUP BY ArticleID

spinoza
Starting Member

49 Posts

Posted - 2006-01-22 : 06:34:19

Thank you very much both of them...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources