[SQL] Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

This is a general problem for a newcomer to SQL Server.

select avg(count(ip)) from pagehits where [month] = 2 group by ip

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.

Solution - use a derived table:

select avg(ipcount) from (select count(ip) ipcount from pagehits where [month] = 2 group by ip) as sub

I'm posting this because searches on the error message didn't return good results, so if someone else has this problem (read: when I forget this again) this may save some frustration.