How to get the Average of a Row After Getting Count?

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

>Solution :

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(officer_id)/COUNT(distinct(officer_id)) as 'Average Crimes per Officer'
FROM crime_officers;

Leave a Reply