Statistics - Population

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*/