Msg 130 - Cannot perform an aggregate function on an expression

Sometime you may got the below error when running select query:


Server: Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression 
containing an aggregate or a subquery.


The reason of this error is included in the error message itself. This error is comming when you are performing an aggregate function such as MAX,MIN,SUM etc on a subquery. Or on another expression which is using an aggregate function.


Example
Suppose we have a table called ExamResult with ExamID, StudentID, Number. From this table, you want to determine which exams or tests have the lowest and highest deviation in terms of the Numbers of the students to determine the tests which are too easy or too hard for the students.To determine the Number deviation, you used the difference between the lowest Number received and the highest Number received for each test. You used the following SELECT statement for this purpose:


SELECT [ExamID],
MAX(MAX([Number]) - MIN([Number])) AS [HighDeviation],
MIN(MAX([Number]) - MIN([Number])) AS [LowDeviation]
FROM [dbo].[ExamResult]
GROUP BY [ExamID]


Then the error will come as above. If you simply want the test with the highest deviation in the Numbers, you can simply sort the output by the difference between the highest Number and lowest Number in descending order, as follows:


SELECT [ExamID], MAX([Number]) - MIN([Number])
FROM [dbo].[ExamResult]
GROUP BY [ExamID]
ORDER BY MAX([Number]) - MIN([Number]) DESC


Or you can use the relative position of the column in the SELECT list to sort in the ORDER BY clause:


SELECT [ExamID], MAX([Number]) - MIN([Number])
FROM [dbo].[ExamResult]
GROUP BY [ExamID]
ORDER BY 2 DESC


Similarly, if you simply want the test with the lowest deviation in the Numbers, you can simply sort the output by the difference between the highest Number and lowest Number in ascending order, as follows:


SELECT [ExamID], MAX([Number]) - MIN([Number])
FROM [dbo].[ExamResult]
GROUP BY [ExamID]
ORDER BY MAX([Number]) - MIN([Number]) ASC


Now, if you want to return the highest and lowest deviation for each exam in a single result set, you have to use a sub-query or a derived table for this purpose:


SELECT [ExamID], MAX([Deviation]) AS [HighestDeviation],
MIN([Deviation]) AS [LowestDeviation]
FROM (SELECT [ExamID], MAX([Number]) - MIN([Number]) AS [Deviation]
FROM [dbo].[ExamResult]
GROUP BY [ExamID]) A
GROUP BY [ExamID]
ORDER BY [ExamID]