Police Officers by Rank
This page allows you to see the crime picture from a local police perspective. All Police Officers displayed here belong to a Neighbourhood Team. We are aware that there are a number (unknown to us from the public crime data) of other Police Officers who work for the various forces who are not members of Neighbourhood Teams and so we are not displaying the full numbers of Police Officers, for each of the 43 Police Forces. There are just over 23,000 Neighbourhood Team Officers on the database in England and Wales but not all of them are of the same rank, work full-time or have an evenly distributed quantity or seriousness of crime to deal with. Some are even shared between neighbourhoods.
SELECT pf.title AS PoliceForce ,pf.forceid ,pf.population ,SUM(CASE t.fk_rankid WHEN 1 THEN 1 ELSE 0 END) AS Total1 ,SUM(CASE t.fk_rankid WHEN 2 THEN 1 ELSE 0 END) AS Total2 ,SUM(CASE t.fk_rankid WHEN 3 THEN 1 ELSE 0 END) AS Total3 ,SUM(CASE t.fk_rankid WHEN 4 THEN 1 ELSE 0 END) AS Total4 ,SUM(CASE t.fk_rankid WHEN 5 THEN 1 ELSE 0 END) AS Total5 ,SUM(CASE WHEN t.fk_rankid=0 THEN 1 WHEN t.fk_rankid>5 THEN 1 ELSE 0 END) AS OtherTotal FROM policeforce pf INNER JOIN import_team_n2 t ON (pf.forceid = t.fk_policeforceid) INNER JOIN crimeyear cy ON (cy.ID = t.fk_yearid) WHERE cy.ID =100 GROUP BY pf.title, pf.forceid, pf.population ORDER BY pf.title