Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create table extend_reporter.stats_pop (
- org int,
- stats_year int,
- poverty_percent float,
- population int
- );
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (129,6,15.7,190640);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (153,6,25.7,56876);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (3,6,12.9,171838);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (108,6,20,15055);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (112,6,25.2,32578);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (177,6,29.4,46197);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (171,6,28.1,37788);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (118,6,12.9,145397);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (140,6,22.8,23109);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (144,6,21.3,138326);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (191,6,22.9,34355);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (160,6,18.5,62516);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (194,6,18.5,80458);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (198,6,30.4,18347);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (2,6,0,0);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (106,6,20.3,28030);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (166,6,30.9,33067);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (119,6,15.1,239363);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (129,5,15.7,189355);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (153,5,31.3,56547);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (3,5,12.9,168049);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (108,5,20,14910);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (112,5,25.2,46103);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (177,5,29.4,32546);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (171,5,28.1,38153);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (118,5,12.9,142496);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (140,5,22.8,23363);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (144,5,21.3,137948);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (191,5,22.9,34357);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (160,5,18.5,62343);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (194,5,18.5,79089);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (198,5,30.4,18654);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (2,5,0,0);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (106,5,20.3,28252);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (166,5,30.9,33620);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (119,5,15.1,234635);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (129,4,15.7,188488);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (153,4,31.3,56197);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (3,4,12.9,164684);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (108,4,20,15145);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (112,4,25.2,46557);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (177,4,29.4,32916);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (171,4,28.1,38611);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (118,4,12.9,140892);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (140,4,22.8,23571);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (144,4,21.3,137862);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (191,4,22.9,34726);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (160,4,18.5,62273);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (194,4,18.5,77908);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (198,4,30.4,18976);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (106,4,20.3,28679);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (166,4,30.9,34084);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (119,4,15.1,230528);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (129,3,18.7,187126);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (153,3,30,56286);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (3,3,13,162233);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (108,3,18.5,15175);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (112,3,23.7,46734);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (177,3,20.8,33140);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (171,3,22.6,38892);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (118,3,11.7,136555);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (140,3,23.4,23956);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (144,3,21.7,136885);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (191,3,27.8,34971);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (160,3,18.3,61697);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (194,3,19.7,76652);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (198,3,27.1,19220);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (2,3,0,0);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (106,3,18.7,28961);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (166,3,32.2,34423);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (119,3,13.1,226073);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (129,2,16.8,182937);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (153,2,30.13,54149);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (3,2,12,152164);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (108,2,17.1,14642);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (112,2,23.6,43072);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (177,2,19.5,32732);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (171,2,22,39349);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (118,2,13.3,127830);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (140,2,21,23458);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (144,2,18.4,133368);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (191,2,24.4,33104);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (160,2,14.9,59309);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (194,2,19.8,75872);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (198,2,31.7,20005);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (2,2,0,0);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (106,2,21.9,27644);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (166,2,33.1,34909);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (119,2,12.4,220032);
- insert into extend_reporter.stats_pop (org,stats_year,poverty_percent,population) values (1,6,17.6,4774839);
- /* let's say we want to see how much circulation we had compared to how many pops we have */
- -- problem pop.org is county, circs.circ_lib is branch
- select aou2.shortname as "Library FY 14/15", sum(circs.circs) as circs, pop.population,
- to_char((100*(sum(circs.circs)/pop.population)),'9999') || '%' as "Cics as % of Pop",
- to_char(round(pop.poverty_percent),'99') || '%' as poverty
- from extend_reporter.stats_circs circs
- join actor.org_unit aou on aou.id = circs.circ_lib
- join actor.org_unit aou2 on aou2.id = aou.parent_ou
- join extend_reporter.stats_pop pop on pop.org = aou2.id
- where pop.stats_year = 5 and aou2.shortname != 'STATELIB'
- group by 1, 3, 5 order by 1 asc
- ;
- /* this isn't directly replicatable in the reporter because if you start with circs you go to aou -> parent and
- can't get back to the population
- what if I join pop to org unit and say where pop.org = aou2.id?
- nevermind, reporter won't let me do that
- I could create mappings and make it work for A single report but it wouldn't accomplish the flexiblity we want*/
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement