As of 19th Jan 2011
/* suite of sql scripts which give rough analyis of data in the University Card database */
/* it is the best indicator we have for the composition of the collegiate university */
/* as it include all sorts of people who may no be represented in other data sources eg payroll or student records*/

/* total current card records*/

+-----+
|col1 |
+-----+
|42675|
+-----+
(1 row)


/* by card category*/

+----------+-----+
|status    |total|
+----------+-----+
|undergrad |12039|
|postgrad  | 9747|
|staff     | 7186|
|senmem    | 4243|
|college   | 3079|
|dept      | 1900|
|visitor   | 1805|
|ret       | 1233|
|cardholder|  721|
|student   |  532|
|virtual   |  190|
+----------+-----+
(11 rows)


/* total people per division - exclude colleges */

+--------------------------------------+-----+
|division_name                         |total|
+--------------------------------------+-----+
|Medical Sciences                      | 7293|
|Social Sciences                       | 5924|
|Maths, Physical & Life Sciences       | 5276|
|Humanities                            | 3633|
|University Administration and Services| 1866|
|Academic Services                     | 1449|
|Continuing Education                  | 1293|
|not on tree                           |  101|
|Societies, Other & Companies          |   81|
|Council Departments                   |   33|
+--------------------------------------+-----+
(10 rows)


/* undergrads by OSS division of their course- taken from OSS*/

+-------------------------------+-----+
|division_name                  |total|
+-------------------------------+-----+
|Humanities                     | 4197|
|Maths, Physical & Life Sciences| 3559|
|Social Sciences                | 2163|
|Medical Sciences               | 1676|
+-------------------------------+-----+
(4 rows)


/* by status*/

+--------------------------------------+----------+-----+
|division_name                         |status    |total|
+--------------------------------------+----------+-----+
|Academic Services                     |staff     |  975|
|Academic Services                     |ret       |  169|
|Academic Services                     |dept      |  153|
|Academic Services                     |senmem    |  114|
|Academic Services                     |cardholder|   28|
|Academic Services                     |visitor   |    8|
|Academic Services                     |college   |    2|
|Continuing Education                  |postgrad  |  579|
|Continuing Education                  |undergrad |  444|
|Continuing Education                  |staff     |  116|
|Continuing Education                  |dept      |   70|
|Continuing Education                  |senmem    |   32|
|Continuing Education                  |virtual   |   28|
|Continuing Education                  |ret       |   11|
|Continuing Education                  |visitor   |    9|
|Continuing Education                  |cardholder|    2|
|Continuing Education                  |college   |    2|
|Council Departments                   |staff     |   28|
|Council Departments                   |ret       |    3|
|Council Departments                   |dept      |    2|
|Humanities                            |postgrad  | 1794|
|Humanities                            |senmem    | 1072|
|Humanities                            |visitor   |  236|
|Humanities                            |staff     |  233|
|Humanities                            |ret       |   97|
|Humanities                            |dept      |   87|
|Humanities                            |college   |   39|
|Humanities                            |cardholder|   33|
|Humanities                            |virtual   |   24|
|Humanities                            |student   |   18|
|Maths, Physical & Life Sciences       |postgrad  | 2338|
|Maths, Physical & Life Sciences       |staff     | 1326|
|Maths, Physical & Life Sciences       |senmem    |  787|
|Maths, Physical & Life Sciences       |visitor   |  290|
|Maths, Physical & Life Sciences       |ret       |  232|
|Maths, Physical & Life Sciences       |dept      |  201|
|Maths, Physical & Life Sciences       |cardholder|   58|
|Maths, Physical & Life Sciences       |virtual   |   24|
|Maths, Physical & Life Sciences       |college   |   14|
|Maths, Physical & Life Sciences       |student   |    6|
|Medical Sciences                      |staff     | 2659|
|Medical Sciences                      |postgrad  | 1356|
|Medical Sciences                      |senmem    | 1059|
|Medical Sciences                      |dept      |  911|
|Medical Sciences                      |undergrad |  515|
|Medical Sciences                      |visitor   |  434|
|Medical Sciences                      |ret       |  169|
|Medical Sciences                      |cardholder|  142|
|Medical Sciences                      |virtual   |   33|
|Medical Sciences                      |student   |    8|
|Medical Sciences                      |college   |    7|
|Social Sciences                       |postgrad  | 3680|
|Social Sciences                       |staff     |  710|
|Social Sciences                       |senmem    |  698|
|Social Sciences                       |visitor   |  464|
|Social Sciences                       |dept      |  178|
|Social Sciences                       |ret       |   93|
|Social Sciences                       |cardholder|   51|
|Social Sciences                       |virtual   |   26|
|Social Sciences                       |student   |   14|
|Social Sciences                       |college   |   10|
|Societies, Other & Companies          |staff     |   62|
|Societies, Other & Companies          |dept      |   12|
|Societies, Other & Companies          |visitor   |    3|
|Societies, Other & Companies          |senmem    |    2|
|Societies, Other & Companies          |cardholder|    1|
|Societies, Other & Companies          |ret       |    1|
|University Administration and Services|staff     | 1054|
|University Administration and Services|senmem    |  312|
|University Administration and Services|dept      |  193|
|University Administration and Services|ret       |  165|
|University Administration and Services|cardholder|  130|
|University Administration and Services|visitor   |    6|
|University Administration and Services|virtual   |    5|
|University Administration and Services|college   |    1|
|not on tree                           |dept      |   93|
|not on tree                           |senmem    |    6|
|not on tree                           |cardholder|    1|
|not on tree                           |ret       |    1|
+--------------------------------------+----------+-----+
(79 rows)


/* number per dept in card */

+------------------------------+----+
|nickname                      |tot |
+------------------------------+----+
|Continuing Education          |1293|
|Said Business School          |1050|
|Bodleian                      | 825|
|History                       | 821|
|University Offices            | 811|
|Engineering Science           | 726|
|Law                           | 720|
|NDM                           | 713|
|Computing Lab.                | 663|
|Politics                      | 634|
|Education                     | 614|
|Maths                         | 563|
|Medical School Offices        | 515|
|Physiology and Anatomy        | 484|
|Environment                   | 483|
|Oriental Institute            | 475|
|Chemistry                     | 467|
|Zoology                       | 465|
|English                       | 464|
|Biochemistry                  | 463|
|Modern Languages              | 426|
|Classics                      | 392|
|Queen Elizabeth House         | 386|
|Economics                     | 368|
|Theology                      | 365|
|Ashmolean Museum              | 357|
|Materials                     | 354|
|Public Health (Admin)         | 349|
|Experimental Psychology       | 338|
|Pathology                     | 319|
|Philosophy                    | 307|
|Anthropology                  | 292|
|Psychiatry                    | 287|
|Wellcome Genetics             | 238|
|Clinical Neurology            | 237|
|NDORMS                        | 233|
|Pharmacology                  | 226|
|Physics                       | 225|
|Surgical Sciences             | 224|
|Archaeology                   | 207|
|Clinical Trial Service        | 206|
|Molecular Medicine            | 206|
|Plant Sciences                | 201|
|Computing Services            | 200|
|Social Policy                 | 200|
|Radiation Oncology            | 200|
|Inorganic Chemistry           | 199|
|Estates Directorate           | 197|
|Physical/Theoretical Chemistry| 188|
|Earth Sciences                | 179|
|Finance                       | 179|
|Clin. Lab. Sciences           | 174|
|Particle Physics              | 172|
|Condensed Matter Physics      | 163|
|Statistics                    | 162|
|Cardiovascular Medicine       | 160|
|Said Business School Ltd      | 152|
|Area Studies                  | 147|
|Sociology                     | 146|
|Paediatrics                   | 141|
|Obstetrics/Gynaecology        | 140|
|Music                         | 137|
|Astrophysics                  | 128|
|Medical Sciences Office       | 116|
|BSP                           | 115|
|Medical Sciences Division     | 114|
|Clinical Pharmacology         | 111|
|OCDEM                         | 110|
|Biomedical Services           | 108|
|Atomic Physics                | 106|
|Anaesthetics                  | 105|
|Tropical Medicine             | 100|
|Jenner Institute              |  97|
|Oncology                      |  97|
|Development Office            |  97|
|Theoretical Physics           |  96|
|Atmospheric Physics           |  93|
|Internet Institute            |  91|
|Linguistics Philology         |  88|
|Perinatal Epidemiology        |  87|
|Pitt Rivers                   |  86|
|Archaeology Research          |  85|
|Structural Genomics           |  85|
|OUP                           |  75|
|Med Sci DTC                   |  74|
|Sport                         |  72|
|Isis Innovation               |  69|
|University Museum             |  60|
|Ophthalmology                 |  59|
|African Studies               |  55|
|Med Sci Teach Cent            |  49|
|Doctoral Training (MPLS)      |  48|
|Ruskin School                 |  46|
|OeRC                          |  44|
|Latin American Centre         |  41|
|Smith School                  |  40|
|Careers Service               |  40|
|Language Centre               |  40|
|ICT Support Team              |  35|
|Ludwig Institute              |  34|
|Socio-Legal Studies           |  32|
|Criminology                   |  31|
|Learning Institute            |  28|
|Botanic Garden                |  28|
|COMPAS                        |  27|
|Rhodes House                  |  26|
|History of Science            |  26|
|University Club               |  26|
|European/Comparative Law      |  25|
|Statistical Medicine          |  24|
|Counselling Service           |  24|
|Veterinary Services           |  23|
|Nissan Institute              |  22|
|Begbroke                      |  22|
|Voltaire Foundation           |  21|
|IMSU                          |  20|
|Humanities Division           |  18|
|Oxford Man                    |  18|
|History of Medicine           |  17|
|Childhood Cancer Research     |  17|
|Occupational Health Service   |  16|
|Rothermere Institute          |  16|
|Safety Office                 |  16|
|Ageing                        |  15|
|Alumni                        |  14|
|Social Sciences Division      |  14|
|Martin School                 |  13|
|Chinese Studies               |  13|
|Student Union                 |  12|
|Maths Physics Life Division   |  12|
|Proctors' Office              |  12|
|Phonetics                     |  12|
|Human Sciences                |  11|
|History of Art                |   9|
|Said Business School Library  |   8|
|Sheldonian Theatre            |   7|
|Cont Ed Library               |   6|
|NDM Strategic                 |   6|
|EHRC                          |   6|
|Education Library             |   5|
|Conference Oxford             |   4|
|Brain Activity                |   4|
|University Archives           |   4|
|Spinout Equity Mgmt           |   4|
|Blavatnik                     |   3|
|Latin American Library        |   3|
|Director of IT                |   2|
|Zoology Library               |   1|
|Geography                     |   1|
|Brazilian Studies             |   1|
+------------------------------+----+
(150 rows)

/* number per college in card */

+------------------+----+
|nickname          |tot |
+------------------+----+
|Wolfson           |1163|
|St Catherine's    |1027|
|Balliol           | 945|
|Christ Church     | 945|
|New               | 932|
|Magdalen          | 912|
|St Anne's         | 898|
|St Hugh's         | 885|
|Keble             | 862|
|Wadham            | 856|
|St John's         | 856|
|Merton            | 829|
|St Edmund Hall    | 828|
|Lady Margaret Hall| 802|
|Exeter            | 797|
|Kellogg           | 786|
|Hertford          | 779|
|Worcester         | 776|
|Brasenose         | 770|
|Lincoln           | 753|
|Green Templeton   | 745|
|University        | 743|
|Jesus             | 726|
|St Hilda's        | 716|
|Pembroke          | 708|
|St Antony's       | 707|
|Somerville        | 659|
|St Cross          | 659|
|Oriel             | 654|
|Queen's           | 651|
|Linacre           | 621|
|St Peter's        | 604|
|Trinity           | 537|
|Corpus Christi    | 516|
|Mansfield         | 464|
|Harris Manchester | 315|
|Nuffield          | 299|
|Regent's Park     | 230|
|Wycliffe Hall     | 177|
|All Souls         | 152|
|Blackfriars       |  92|
|St Benet's Hall   |  78|
|St Stephen's House|  74|
|Ripon             |  72|
|Campion Hall      |  38|
+------------------+----+
(45 rows)

Wed Jan 19 16:03:04 2011