Thu Jan 19 12:59:34 2012 /* 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*/ /* script location: /srv/babylon/svn/live/projects/unit_management/sql */ /* total current card records*/ +-----+ |total| +-----+ |44272| +-----+ (1 row) /* by card category*/ +----------+-----+ |status |total| +----------+-----+ |undergrad |12113| |postgrad |10315| |staff | 7439| |senmem | 4310| |college | 3193| |dept | 2044| |visitor | 1919| |ret | 1337| |cardholder| 838| |student | 558| |virtual | 205| +----------+-----+ (12 rows) /* total people per division - exclude colleges */ +--------------------------------------+-----+ |division_name |total| +--------------------------------------+-----+ |Medical Sciences | 7625| |Social Sciences | 6187| |Maths, Physical & Life Sciences | 5563| |Humanities | 3785| |University Administration and Services| 1988| |Academic Services | 1439| |Continuing Education | 1356| |Societies, Other & Companies | 122| |not on tree | 107| |Council Departments | 6| +--------------------------------------+-----+ (10 rows) /* undergrads by OSS division of their course- taken from OSS*/ +-------------------------------+-----+ |division_name |total| +-------------------------------+-----+ |Humanities | 4224| |Maths, Physical & Life Sciences| 3597| |Social Sciences | 2218| |Medical Sciences | 1687| +-------------------------------+-----+ (4 rows) /* by card status*/ +--------------------------------------+----------+-----+ |division_name |status |total| +--------------------------------------+----------+-----+ |Academic Services |staff | 917| |Academic Services |ret | 176| |Academic Services |dept | 119| |Academic Services |senmem | 119| |Academic Services |cardholder| 92| |Academic Services |visitor | 11| |Academic Services |college | 5| |Continuing Education |postgrad | 695| |Continuing Education |undergrad | 386| |Continuing Education |staff | 114| |Continuing Education |dept | 77| |Continuing Education |senmem | 32| |Continuing Education |virtual | 26| |Continuing Education |ret | 11| |Continuing Education |visitor | 11| |Continuing Education |cardholder| 2| |Continuing Education |college | 2| |Council Departments |staff | 4| |Council Departments |ret | 2| |Humanities |postgrad | 1892| |Humanities |senmem | 1067| |Humanities |visitor | 289| |Humanities |staff | 251| |Humanities |ret | 108| |Humanities |dept | 101| |Humanities |college | 39| |Humanities |cardholder| 22| |Humanities |student | 8| |Humanities |virtual | 8| |Maths, Physical & Life Sciences |postgrad | 2508| |Maths, Physical & Life Sciences |staff | 1323| |Maths, Physical & Life Sciences |senmem | 800| |Maths, Physical & Life Sciences |visitor | 309| |Maths, Physical & Life Sciences |ret | 255| |Maths, Physical & Life Sciences |dept | 231| |Maths, Physical & Life Sciences |cardholder| 79| |Maths, Physical & Life Sciences |virtual | 29| |Maths, Physical & Life Sciences |college | 19| |Maths, Physical & Life Sciences |student | 10| |Medical Sciences |staff | 2857| |Medical Sciences |postgrad | 1396| |Medical Sciences |senmem | 1076| |Medical Sciences |dept | 1016| |Medical Sciences |undergrad | 515| |Medical Sciences |visitor | 396| |Medical Sciences |ret | 190| |Medical Sciences |cardholder| 130| |Medical Sciences |virtual | 40| |Medical Sciences |student | 5| |Medical Sciences |college | 4| |Social Sciences |postgrad | 3824| |Social Sciences |staff | 771| |Social Sciences |senmem | 706| |Social Sciences |visitor | 511| |Social Sciences |dept | 172| |Social Sciences |ret | 104| |Social Sciences |cardholder| 36| |Social Sciences |virtual | 32| |Social Sciences |student | 18| |Social Sciences |college | 13| |Societies, Other & Companies |staff | 93| |Societies, Other & Companies |dept | 19| |Societies, Other & Companies |visitor | 5| |Societies, Other & Companies |senmem | 4| |Societies, Other & Companies |cardholder| 1| |University Administration and Services|staff | 1070| |University Administration and Services|senmem | 332| |University Administration and Services|dept | 211| |University Administration and Services|ret | 184| |University Administration and Services|cardholder| 167| |University Administration and Services|virtual | 11| |University Administration and Services|visitor | 9| |University Administration and Services|college | 4| |not on tree |dept | 98| |not on tree |senmem | 6| |not on tree |cardholder| 1| |not on tree |ret | 1| +--------------------------------------+----------+-----+ (77 rows) /* number per dept in card */ +-------------------------------+----+ |nickname |tot | +-------------------------------+----+ |Continuing Education |1356| |Said Business School |1079| |History | 851| |Engineering Science | 749| |Law | 699| |Computer Science | 691| |Education | 671| |Politics | 635| |NDM | 597| |Maths | 580| |Bodleian | 554| |Medical School Offices | 515| |Environment | 507| |English | 499| |Zoology | 495| |Physiology and Anatomy | 492| |Oncology | 481| |Oriental Institute | 477| |Biochemistry | 456| |Modern Languages | 445| |International Development (QEH)| 411| |Classics | 400| |Clinical Neurosciences | 382| |Theology | 381| |Economics | 379| |Materials | 364| |Experimental Psychology | 363| |Ashmolean Museum | 360| |Pathology | 330| |Philosophy | 316| |Anthropology | 314| |Psychiatry | 297| |Archaeology | 281| |Chemistry | 262| |Inorganic Chemistry | 254| |NDORMS | 246| |Physical/Theoretical Chemistry | 241| |Surgical Sciences | 241| |Clinical Trial Service | 214| |Computing Services | 214| |Public Health | 208| |Estates Directorate | 208| |University Offices | 207| |Physics | 206| |Social Policy | 205| |Pharmacology | 194| |Plant Sciences | 193| |Finance | 190| |Earth Sciences | 190| |Wellcome Genetics | 189| |Condensed Matter Physics | 184| |Clin. Lab. Sciences | 180| |Particle Physics | 180| |Statistics | 175| |Primary Health Care | 173| |Cardiovascular Medicine | 169| |Music | 166| |Organic Chemistry | 154| |Molecular Medicine | 154| |Paediatrics | 154| |Kennedy | 149| |Sociology | 147| |Obstetrics/Gynaecology | 147| |Astrophysics | 145| |Said Business School Ltd | 144| |Tropical Medicine | 142| |BSP | 142| |Medical Sciences Office | 137| |Biomedical Services | 125| |OCDEM | 124| |Atomic Physics | 115| |Area Studies | 113| |Medical Sciences Division | 109| |Atmospheric Physics | 107| |Jenner Institute | 107| |Development Office | 105| |Internet Institute | 105| |Theoretical Physics | 103| |Med Sci DTC | 103| |Bodleian Special Collections | 96| |Student Admin | 88| |Structural Genomics | 85| |Isis Innovation | 83| |Linguistics Philology | 82| |Perinatal Epidemiology | 81| |OUP | 81| |Pitt Rivers | 78| |Research Services | 77| |Criminology | 68| |Security Services | 67| |Sport | 67| |Cancer Epidem. Unit | 64| |University Museum | 61| |Reader Services | 57| |Archaeology Research | 57| |African Studies | 54| |Ruskin School | 54| |Doctoral Training (MPLS) | 52| |OeRC | 51| |Language Centre | 46| |Socio-Legal Studies | 45| |Conservation & Collection Care | 44| |Personnel | 43| |Careers Service | 43| |Latin American Centre | 42| |Bodleian Enterprise Units | 42| |Smith School | 42| |Ludwig Institute | 38| |ICT Support Team | 35| |Structural Biology | 35| |Parks | 34| |Botanic Garden | 33| |Graduate Admissions | 33| |Facilities Management | 32| |Learning Institute | 31| |Public Affairs | 30| |Undergraduate Admissions | 30| |Anaesthetics | 29| |Divisional Administration | 29| |History of Science | 26| |Ophthalmology | 26| |Disability Advisory | 25| |Counselling Service | 25| |Land Agent | 25| |Rhodes House | 25| |Oxford Man | 24| |Begbroke Science Park | 24| |Nissan Institute | 24| |Humanities Division | 24| |FM & Messenger Service | 24| |COMPAS | 24| |Ageing | 23| |University Club | 22| |Chinese Studies(Social Sci) | 21| |European/Comparative Law | 21| |Social Sciences Division | 20| |NDM Strategic | 19| |Voltaire Foundation | 19| |PRAS | 18| |IMSU | 18| |Chemistry Research Laboratory | 17| |Rothermere Institute | 17| |Med Sci Teach Cent | 16| |CCMP | 16| |Alumni | 16| |Childhood Cancer Research | 16| |Oxford Ltd | 16| |Martin School | 16| |Maths Physics Life Division | 16| |History of Medicine | 15| |Occupational Health Service | 15| |Equality | 14| |Safety Office | 14| |Graduate Housing | 14| |Chemical Biology | 14| |Student Union | 14| |Council Secretariat | 14| |Proctors Office | 13| |Legal Services | 13| |History of Art | 12| |Chinese Studies | 9| |Said Business School Library | 9| |Human Sciences | 8| |Phonetics | 8| |Blavatnik | 8| |Education Policy | 7| |Academic Registrar | 7| |Sheldonian Theatre | 6| |Portuguese | 6| |Bodleian Accounts Section | 6| |Cont Ed Library | 6| |Student Services | 5| |Bodleian Libraries Admissions | 5| |Education Library | 4| |Brain Activity | 4| |EHRC | 4| |VC | 4| |Child Care | 4| |University Archives | 4| |Bodleian Estates | 3| |Latin American Library | 3| |Spinout Equity Mgmt | 3| |International Strategy | 3| |Endowment Management | 3| |Conference Oxford | 3| |Official Papers | 2| |Medawar Building | 1| |Director of IT | 1| +-------------------------------+----+ (189 rows) /* number per college in card */ +------------------+----+ |nickname |tot | +------------------+----+ |Wolfson |1246| |St Catherine's |1079| |Balliol | 989| |New | 962| |St Anne's | 947| |Kellogg | 943| |St Hugh's | 938| |Christ Church | 932| |Magdalen | 910| |Keble | 886| |St John's | 880| |Wadham | 864| |St Edmund Hall | 849| |Merton | 839| |Hertford | 817| |Lady Margaret Hall| 811| |Exeter | 796| |Worcester | 788| |Brasenose | 787| |University | 776| |Green Templeton | 773| |Jesus | 772| |Lincoln | 766| |Pembroke | 738| |St Hilda's | 731| |St Antony's | 678| |St Cross | 675| |Somerville | 667| |Oriel | 663| |Linacre | 649| |Queen's | 643| |St Peter's | 623| |Trinity | 557| |Corpus Christi | 525| |Mansfield | 472| |Harris Manchester | 335| |Nuffield | 299| |Regent's Park | 252| |Wycliffe Hall | 220| |All Souls | 167| |Blackfriars | 113| |St Stephen's House| 91| |Ripon | 78| |St Benet's Hall | 75| |Campion Hall | 58| +------------------+----+ (45 rows) Thu Jan 19 12:59:39 2012