martes, 9 de marzo de 2010

Cannot perform an aggregate function on an expression containing an aggregate or a subquery (103 error)

Fix: Error 130: Cannot perform an aggregate function on an expression containing an aggregate or a subquery


Following statement will give the following error: “Cannot perform an aggregate function on an expression containing an aggregate or a subquery.”
MS SQL Server doesn’t support it.


Here you have and example:


USE PUBS
GO



SELECT AVG(COUNT(royalty)) RoyaltyAvg
FROM dbo.roysched
GO


You can get around this problem by breaking out the computation of the average in derived tables.


USE PUBS
GO



SELECT AVG(t.RoyaltyCounts)
FROM
             (SELECT COUNT(royalty) AS RoyaltyCounts FROM dbo.roysched) T
GO


That's all.