Statistics as of Feb 16, 2011. Total number of jobs fired: 42722. # select count(*) from fileproperties; Total number of pages printed: 393274. # select sum(npages) from fileproperties; Total number of users: 437. # select count(*) from users; Total number of reams: 131 # select count(*) from expenditure where itemid=1; First job fired: May 30, 2007. # select * from fileproperties where fileid=1; ---------------------------------------------------------------------- Number of pages printed yearwise. # select sum(npages),date_part('year', date_trunc('year', creationtime)) from fileproperties group by date_trunc('year', creationtime) order by date_trunc('year', creationtime); npages | year --------+----------- 36384 | 2007 101434 | 2008 110448 | 2009 125129 | 2010 19879 | 2011 --------+----------- ---------------------------------------------------------------------- Number of pages printed monthwise. # select sum(npages),date_part('year', date_trunc('month', creationtime)),date_part('month', date_trunc('month', creationtime)) from fileproperties group by date_trunc('month', creationtime) order by date_trunc('month', creationtime); npages| year | month -------+-----------+----------- 108 | 2007 | 5 5248 | 2007 | 6 4320 | 2007 | 7 4039 | 2007 | 8 6399 | 2007 | 9 5919 | 2007 | 10 8273 | 2007 | 11 2078 | 2007 | 12 5830 | 2008 | 1 8592 | 2008 | 2 11052 | 2008 | 3 5656 | 2008 | 4 2972 | 2008 | 5 9289 | 2008 | 6 6037 | 2008 | 7 8607 | 2008 | 8 13380 | 2008 | 9 13228 | 2008 | 10 10995 | 2008 | 11 5796 | 2008 | 12 9744 | 2009 | 1 11658 | 2009 | 2 9267 | 2009 | 3 9056 | 2009 | 4 5899 | 2009 | 5 10903 | 2009 | 6 6074 | 2009 | 7 9121 | 2009 | 8 11921 | 2009 | 9 13201 | 2009 | 10 9646 | 2009 | 11 3958 | 2009 | 12 12892 | 2010 | 1 15984 | 2010 | 2 14169 | 2010 | 3 8247 | 2010 | 4 4782 | 2010 | 5 8056 | 2010 | 6 4306 | 2010 | 7 10977 | 2010 | 8 13632 | 2010 | 9 13862 | 2010 | 10 13102 | 2010 | 11 5120 | 2010 | 12 11938 | 2011 | 1 7941 | 2011 | 2 -------+-----------+----------- ---------------------------------------------------------------------- Number of pages printed monthwise across years. # select sum(npages),date_part('month', creationtime) from fileproperties group by date_part('month', creationtime) order by date_part('month', creationtime); npages| month (across all years) -------+----------- 40404 | 1 44175 | 2 34488 | 3 22959 | 4 13761 | 5 33496 | 6 20737 | 7 32744 | 8 45332 | 9 46210 | 10 42016 | 11 16952 | 12 -------+----------- ---------------------------------------------------------------------- Administrators. # select username from administrator,users where adminid=userid order by userid; mitesh rashmin Shobhit Singh Jayakumar H G Rupesh Nasre Sreedal Menon Aditya M Jithin Vachery. Hemant Salwan. ---------------------------------------------------------------------- Expenditure by administrators. # select username, sum(price*quantity) from expenditure,administrator,users where administrator.adminid=userid and userid=expenditure.adminid group by administrator.adminid, username order by sum(price*quantity); username | sum ----------------+---------- Hemant Salwan. | 490.00 Aditya M | 700.00 mitesh | 1330.00 Sreedal Menon | 2050.00 Shobhit Singh | 6620.00 Rupesh Nasre | 93176.00 ----------------+---------- ---------------------------------------------------------------------- Expenditure yearwise. # select date_part('year', date_trunc('year', buydate)), sum(price*quantity) from expenditure where itemid=1 group by date_trunc('year', buydate) order by date_part('year', date_trunc('year', buydate)); year | sum -----------+---------- 2006 | 270.00 2007 | 6470.00 2008 | 18615.00 2009 | 22320.00 2010 | 22100.00 2011 | 2640.00 -----------+---------- ---------------------------------------------------------------------- Expenditure monthwise. # select date_part('year', date_trunc('month', buydate)), date_part('month', date_trunc('month', buydate)),sum(price*quantity) from expenditure where itemid=1 group by date_trunc('month', buydate) order by date_part('year', date_trunc('month', buydate)), date_part('month', date_trunc('month', buydate)); year | month | sum -----------+-----------+--------- 2006 | 6 | 270.00 2007 | 6 | 440.00 2007 | 7 | 1230.00 2007 | 8 | 510.00 2007 | 9 | 1500.00 2007 | 10 | 900.00 2007 | 11 | 1140.00 2007 | 12 | 750.00 2008 | 1 | 900.00 2008 | 2 | 2100.00 2008 | 3 | 1110.00 2008 | 4 | 730.00 2008 | 5 | 450.00 2008 | 6 | 2140.00 2008 | 7 | 900.00 2008 | 8 | 1350.00 2008 | 9 | 2010.00 2008 | 10 | 3300.00 2008 | 11 | 1275.00 2008 | 12 | 2350.00 2009 | 1 | 1400.00 2009 | 2 | 1750.00 2009 | 3 | 2100.00 2009 | 4 | 1400.00 2009 | 5 | 2100.00 2009 | 6 | 2040.00 2009 | 7 | 1710.00 2009 | 8 | 1340.00 2009 | 9 | 2720.00 2009 | 10 | 2560.00 2009 | 11 | 1920.00 2009 | 12 | 1280.00 2010 | 1 | 1920.00 2010 | 2 | 2720.00 2010 | 3 | 1880.00 2010 | 4 | 1200.00 2010 | 5 | 1240.00 2010 | 6 | 1260.00 2010 | 7 | 990.00 2010 | 8 | 1650.00 2010 | 9 | 2640.00 2010 | 10 | 1980.00 2010 | 11 | 2640.00 2010 | 12 | 1980.00 2011 | 1 | 1320.00 2011 | 2 | 1320.00 -----------+-----------+---------