List the name of each officer who has reported more than the average number of crimes officers have reported.

Question

NEED THE QUERY

Answer 1

If the data is in one table (I'll call it officers) with two columns, officer_name and number_crimes, this query will do it:

SELECT officer_name

FROM officers

WHERE number_crimes >

(SELECT AVG(number_crimes)

FROM officers);

The average number of crimes officers have reported is given by the subquery. The database will first evaluate the subquery, then replace the subquery with that average number, and the outer query will find the names of the officers who have reported more than the average number of crimes.

Answer 2

You can use count, having and where clause to solve this problem...you can hit me up to explain further

Answer 3

You can use Having clause to perform this. If you examine the flow of SQL query execution:

 

1. From clause.

2. where clause.

3. Group By clause.

4. Having clause.

 

Having clause is computed after group by clause. So, you can use aggregate function in Having clause to check the condition.