SQL Server Performance

Group by working in SQL Server 2000 but not in SQL Server 2005

Discussion in 'SQL Server 2005 General Developer Questions' started by jiten27, Jul 3, 2011.

  1. jiten27 New Member

    Hello,
    I used following query in SQL Server 2000

    Code:
    SELECT U.FirstName,SUM(VE.Score)AS Score, SUM(VE.QuizTime)AS Time,SUM(VE.IsQuizType)AS QuizesAttempted,SUM(VE.IsProgrammingType)AS ProgrammingProblemsAttempted from Users U INNER JOIN VirtualExercise VE on U.UserID=VE.UserID where U.UserID IN( 10,11 ) AND ProgramID = 2 group by U.FirstName order by VE.Score desc
    It working fine in SQL Server 2000 but not working in SQL Server 2005.
    Gives following error:

    Column "VirtualExercise.Score" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. --- Inner Exception

    Please help...
  2. FrankKalis Moderator

    Welcome to the forum!
    Is that the full statement you are executing or just a part thereof?
  3. satya Moderator

  4. SQL Server Helper New Member

    As the error suggests, the error is caused by the ORDER BY VE.Score because you don't have VE.Score in your SELECT statement and GROUP BY statement. To solve this issue, simply change the ORDER BY VE.Score DESC with ORDER BY Score DESC (without the VE alias).

    Regards,
    SQL Server Helper
  5. FrankKalis Moderator

    To expand on this: If you remove the VE. in the ORDER BY clause, you are referring to the column alias Score, and not the actual column (VE.)score. Alternatively you can just repeat the aggregate SUM(VE.Score) to achieve the same result.

Share This Page