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.

SQL Server error 130

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *