SQL Server Error Messages - Msg 147

Error Message

Server: Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Causes:

This error occurs when you include an aggregate function such as MAX, MIN, AVG or COUNT in your WHERE clause. To illustrate, let’s say you have the following table that contains all your customers:

CREATE TABLE [dbo].[Customers] (
    [CustomerID]    INT,
    [FirstName]     VARCHAR(50),
    [LastName]      VARCHAR(50)
)

From this table, you want to determine the duplicate records based on the customer’s first name and last name by issuing the following SELECT statement:

SELECT [FirstName], [LastName], COUNT(*)
FROM [dbo].[Customers]
WHERE COUNT(*) > 1
GROUP BY [FirstName], [LastName]

Issuing this SELECT statement will generate the following error message:

Server: Msg 147, Level 15, State 1, Line 3
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

As another example, let’s say you have the following table that contains the salaries of your employees:

CREATE TABLE [dbo].[EmployeeSalary] (
    [EmployeeID]        INT,
    [Salary]            MONEY
)

From this table, you want to generate a list of employees whose salary is higher than the average salary of all employees. To accomplish this requirement, you issued the following SELECT statement:

SELECT [EmployeeID], [Salary]
FROM [dbo].[EmployeeSalary]
WHERE [Salary] >= AVG([Salary])

Issuing this SELECT statement, just like the first one, will also generate the same error message:

Server: Msg 147, Level 15, State 1, Line 3
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Solution / Work Around

If you need to filter out the records from your SELECT statement with the values from an aggregate function, you have to specify the condition in the HAVING clause of the SELECT statement and not in the WHERE clause.

For the first example above, to get the duplicate customer records based on the first name and last name, the correct SELECT statement is as follows:

SELECT [FirstName], [LastName], COUNT(*)
FROM [dbo].[Customers]
GROUP BY [FirstName], [LastName]
HAVING COUNT(*) > 1

The only difference between this corrected SELECT statement and the one above is the moving of the COUNT(*) > 1 condition from the WHERE clause to the HAVING clause because it is using an aggregate function, in this case the COUNT aggregate function.

For the second example above, you will need to use a sub-query to get the list of employees whose salary is higher than the average salary of all employees:

SELECT [EmployeeID], [Salary]
FROM [dbo].[EmployeeSalary]
WHERE [Salary] >= (SELECT AVG([Salary])
                   FROM [dbo].[EmployeeSalary])