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

To illustrate how this error may be encountered when using an aggregate function on an expression containing another aggregate function, let’s say you have the following table which contains the scores of the students on the different tests or exams:

CREATE TABLE [dbo].[TestScores] (
    [TestID]        INT,
    [StudentID]     INT,
    [Score]         INT
)

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. This determines if the test is either 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]

Issuing this SELECT statement will generate the following error:

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

Solution / Workaround:

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]

This solution works for any aggregate function that you want to perform on another aggregate function.

• Frequently Asked Questions - SQL Server Error Messages
• Tips & Tricks - SQL Server Error Messages