Skip to content

Instantly share code, notes, and snippets.

@skyowen
Last active January 3, 2016 17:09
Show Gist options
  • Save skyowen/8494344 to your computer and use it in GitHub Desktop.
Save skyowen/8494344 to your computer and use it in GitHub Desktop.
Highest number(monthly) of thefts from the person between 2011-2012:
mysql> SELECT MAX(Westminster) FROM April11_12;
+------------------+
| MAX(Westminster) |
+------------------+
| 887 |
+------------------+
mysql> SELECT MAX(Hackney) FROM April11_12;
+--------------+
| MAX(Hackney) |
+--------------+
| 372 |
+--------------+
mysql> SELECT MAX(Tower_Hamlets) FROM April11_12;
+--------------------+
| MAX(Tower_Hamlets) |
+--------------------+
| 232 |
+--------------------+
Average number of thefts from the person in 2011-2012:
mysql> SELECT AVG(Westminster) FROM April11_12;
+------------------+
| AVG(Westminster) |
+------------------+
| 739 |
+------------------+
mysql> SELECT AVG(Hackney) FROM April11_12;
+--------------+
| AVG(Hackney) |
+--------------+
| 227.75 |
+--------------+
mysql> SELECT AVG(Tower_Hamlets) FROM April11_12;
+--------------------+
| AVG(Tower_Hamlets) |
+--------------------+
| 142 |
+--------------------+
Average annual number of thefts from the person commited between 2000-2012:
mysql> SELECT AVG(Westminster) FROM Theft_Person_Years;
+------------------+
| AVG(Westminster) |
+------------------+
| 8046.30769230769 |
+------------------+
mysql> SELECT AVG(Hackney) FROM Theft_Person_Years;
+------------------+
| AVG(Hackney) |
+------------------+
| 2134.53846153846 |
+------------------+
mysql> SELECT AVG(Tower_Hamlets) FROM Theft_Person_Years;
+--------------------+
| AVG(Tower_Hamlets) |
+--------------------+
| 1405.38461538462 |
+--------------------+
Total number of offences committed in London ordered in descending order:
mysql> SELECT Year, London_Total FROM Total_Crime_London ORDER BY London_Total DESC;
+------+--------------+
| Year | London_Total |
+------+--------------+
| 2002 | 1080741 |
| 2003 | 1060930 |
| 2001 | 1057360 |
| 2004 | 1015121 |
| 2000 | 994233 |
| 2005 | 984125 |
| 2006 | 921779 |
| 2007 | 862032 |
| 2008 | 845040 |
| 2009 | 829319 |
| 2010 | 823419 |
| 2011 | 814727 |
| 2012 | 771437 |
+------+--------------+
Total number of thefts from the person committed in London, ordered in descending order:
mysql> SELECT Date,London FROM Theft_Person_Years ORDER BY London DESC;
+------+--------+
| Date | London |
+------+--------+
| 2002 | 51876 |
| 2012 | 49196 |
| 2001 | 48510 |
| 2003 | 46217 |
| 2005 | 42677 |
| 2011 | 42115 |
| 2004 | 42047 |
| 2000 | 38807 |
| 2006 | 37969 |
| 2010 | 35161 |
| 2009 | 33712 |
| 2007 | 33305 |
| 2008 | 28627 |
+------+--------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment