Created
August 16, 2016 02:07
-
-
Save bellerbrock/614c1a2e5a8aa6c2cbd047f0b2e64f8a to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
query for no misisng values: | |
SELECT table1.accession_id, table1.accession_name, table1.trait1, table2.trait2, table3.trait3 from (SELECT accession_id, accession_name, avg(phenotype_value::real) as trait1 FROM materialized_phenoview WHERE trial_id = 122 AND trait_id = 70741 group by 1,2) as table1 join (SELECT accession_id, accession_name, avg(phenotype_value::real) as trait2 FROM materialized_phenoview WHERE trial_id = 122 AND trait_id = 70691 group by 1,2) as table2 using(accession_id) join (SELECT accession_id, accession_name, avg(phenotype_value::real) as trait3 FROM materialized_phenoview WHERE trial_id = 122 AND trait_id = 70762 group by 1,2) as table3 using(accession_id) order by 2; | |
query for allowing missing values: | |
SELECT table0.accession_id, table0.accession_name, table1.trait1, table2.trait2, table3.trait3 from (SELECT accession_id, accession_name from materialized_phenoview WHERE trial_id = 122 group by 1,2) as table0 full outer join (SELECT accession_id, accession_name, avg(phenotype_value::real) as trait1 FROM materialized_phenoview WHERE trial_id = 122 AND trait_id = 70741 group by 1,2) as table1 using (accession_id) full outer join (SELECT accession_id, accession_name, avg(phenotype_value::real) as trait2 FROM materialized_phenoview WHERE trial_id = 122 AND trait_id = 70691 group by 1,2) as table2 using(accession_id) full outer join (SELECT accession_id, accession_name, avg(phenotype_value::real) as trait3 FROM materialized_phenoview WHERE trial_id = 122 AND trait_id = 70762 group by 1,2) as table3 using(accession_id) order by 2; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment