I am trying to use an aggregate function to get the average(count( of a row in SQL Server. However, I continue to get this message:

"Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

In the first picture is the table, in the second picture is the table with the counts for each officer_id, I am trying to find the average amount of calls per officer and cannot seem to find the right SQL query to do it.

The query I thought may work is:

SELECT AVG(COUNT(officer_id))
FROM crime_officers
ORDER BY officer_id;

But this is where I get the aggregate error. Does anyone have any recommendations?

UPDATED table with this query SELECT officer_id, COUNT(crime_id) FROM crime_officers GROUP BY officer_id;

Original table: crime_officers

CodePudding user response：

If I understand correctly, this query provides the average number of crimes per officer. A single value, which is equal to the total number of crimes divided between all officers.

SELECT COUNT(*)*1.0/COUNT(distinct officer_id) as 'Average Crimes per Officer'
FROM crime_officers;