load and count whr.sql

load and count whr.sql

drop table pheno4;
create table pheno4 (sampleindex int, m_id varchar(25), area varchar(1), q1_age_cohent int, q3prelim_waist int, q3prelim_hip int, age_calc float);
load data local infile 'C:/cygwin/home/sturner/mec_gwas/phenotypes/waisthip.csv'
    into table pheno4 
    fields terminated by ','
    ignore 1 lines
    (sampleindex, m_id, area, q1_age_cohent, q3prelim_waist, q3prelim_hip, age_calc)
;
alter table pheno4 add column q3prelim_whr float after q3prelim_hip;
update pheno4 set q3prelim_whr=q3prelim_waist/q3prelim_hip; 
alter table pheno4 add primary key (m_id);
select * from pheno4;

select count(*) from sample_info a join pheno4 b on a.m_id=b.m_id where q3prelim_whr is not null;
select eth, count(*) from sample_info a join pheno4 b on a.m_id=b.m_id where q3prelim_whr is not null group by eth;
select eth, corr_sex, count(*) from sample_info a join pheno4 b on a.m_id=b.m_id where q3prelim_whr is not null group by eth, corr_sex order by eth, corr_sex;