SQL JOIN, AVG, COUNT clarification

Question

I have the following solution to the problem in book. Q: List the name of each officer who has reported greater than the average number of crimes?

    SELECT last, first
   FROM officers JOIN crime_officers
        USING (officer_id)
    JOIN crimes USING (crime_id)
   GROUP BY last, first
   HAVING COUNT(crime_id) > (SELECT AVG(COUNT(crime_id))
                                               FROM crimes JOIN crime_officers
                                                 USING (crime_id)

My question is :

how does avg and count works? does the avg just take total of item in column and divide it by number of entries?
Question ask for name of officers who reported crimes, how does comparison statement(last statement) relates to the officers?

user_74195 · Answer

I THINK what you are describing is as follows, and please clarify if I am incorrect. There is some ultimate crime table such that MIGHT be

Crimes
CrimeID   Crime
1         Burglary
2         Rape
3         Theft
etc

And another table of Officers

Officers
OfficerID  Officer
1          Mary
2          Bob
3          Jane

And as you have

Crime_Officers
CrimeOfficerID  CrimeID  OfficerID
1               3        1
2               2        1
3               3        2
4               3        2
etc

And you are trying to look at -- on a per officer basis, how many in each respective category does the average police officer report of the respective crimes.

To do that, you need to get an average based on the totals per officer first

select
      crimeid,
      officerid,
      count(*) crimeCountPerOfficer
   from
      crime_officers co
   group by
      crimeid,
      officerid

Now that you have the counts per crime and officer, you can now get the average per crime

select
      cc.crimeid,
      avg( cc.crimeCountPerOfficer ) avgPerOfficer
   from
      ( select
              crimeid,
              officerid,
              count(*) crimeCountPerOfficer
           from
              crime_officers
           group by
              crimeid,
              officerid ) cc
   group by
      cc.crimeid

Now that you have the averages per officer, now you can requery the root basis of officers who had at or more than the average. But, since you need to make two calls for the same query of counts per crime and officer, you probably would be best to resolve using a WITH CTE (common table expression), where you are pre-writing one query that will be used TWICE (or more) in the following, which may be slightly different between SQL engines and may need to be adjusted accordingly (SQL-Server, MySQL, Oracle, etc)

WITH PerOfficerCnt (CrimeID, OfficerID, CountOfCrimes)  
AS  
(  
    select
          crimeid,
          officerid,
          count(*) CountOfCrimes
       from
          crime_officers
       group by
          crimeid,
          officerid
)  
SELECT
      c.crime,
      o.officer,
      poc.CountOfCrimes OfficersCount
      crimeAvg.AvgPerOfficer  AvgOfficerCount
   FROM 
      PerOfficerCnt poc
         JOIN
         ( select
                 crimeid,
                 avg( CountOfCrimes ) AvgPerOfficer
              from
                 PerOfficerCnt
              group by
                 crimeid ) CrimeAvg
            ON poc.crimeid = CrimeAvg.crimeid
            AND poc.crimeCountPerOfficer >= CrimeAvg.AvgPerOfficer
         JOIN officers o
            on poc.officerid = o.officerid
         JOIN crimes c
            on poc.crimeid = c.crimeid

SQL JOIN, AVG, COUNT clarification

1 Answers