SQL Server Error Message - Msg 130 - Cannot perform an aggregate function on an expression,sql,query,aggregate,function,subquery

When running select query the following error may come..

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.

To illustate the error we can use an example. Suppose we have a table called Testscores with testid,studentid,score. From this table, you want to determine which exams or tests have the lowest and highest deviation in terms of the scores of the students to determine the tests which are too easy or too hard for the students.To determine the score deviation, you used the difference between the lowest score received and the highest score received for each test. You used the following SELECT statement for this purpose:

SELECT [TestID],
MAX(MAX([Score]) - MIN([Score])) AS [HighDeviation],
MIN(MAX([Score]) - MIN([Score])) AS [LowDeviation]
FROM [dbo].[TestScores]
GROUP BY [TestID]

Then the error will come as above. And the solutions are also very simple

If you simply want the test with the highest deviation in the scores, you can simply sort the output by the difference between the highest score and lowest score in descending order, as follows:

SELECT [TestID], MAX([Score]) - MIN([Score])
FROM [dbo].[TestScores]
GROUP BY [TestID]
ORDER BY MAX([Score]) - MIN([Score]) DESC

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

SELECT [TestID], MAX([Score]) - MIN([Score])
FROM [dbo].[TestScores]
GROUP BY [TestID]
ORDER BY 2 DESC

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

SELECT [TestID], MAX([Score]) - MIN([Score])
FROM [dbo].[TestScores]
GROUP BY [TestID]
ORDER BY MAX([Score]) - MIN([Score]) 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 [TestID], MAX([Deviation]) AS [HighestDeviation],
MIN([Deviation]) AS [LowestDeviation]
FROM (SELECT [TestID], MAX([Score]) - MIN([Score]) AS [Deviation]
FROM [dbo].[TestScores]
GROUP BY [TestID]) A
GROUP BY [TestID]
ORDER BY [TestID]






BLESSED MARY PRAY FOR US


.........................