Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading