SQL Server error 130 – Cannot perform an aggregate function on an expression containing an aggregate

or a sub query

SQL Server throws SQL Server error 130 when parameter to an aggregate function is either an expression or a sub query. An aggregate function such as AVG/MAX/COUNT etc. can’t be done on an expression or a sub query. The parameter should be a column name.

Let’s see an example. The below query creates a table and uses an expression as a parameter to MAX function.

CREATE TABLE tblone (sno INT IDENTITY,col1 VARCHAR(10))
GO
SELECT max(avg(sno)) FROM tblone
GO

The query terminates with below error.

A work around for the same is shown below.

SELECT MAX(avgs.avg_sno) 
FROM
(
	SELECT avg(sno) AS avg_sno FROM tblone 
) AS avgs

The above query succeeds.

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook