CODEWITHSUNDEEP
mysqlbashloopsselectcount
Wicked_sue
Wicked_sue

Reputation: 79

How can I write a loop bash script with a mysql query including subqueries?

So I have this really ugly (but functional) multiple subquery mysql Select command. 

select myt_taxon_list, count(myt_taxon_list)
from (select t1.taxon_list as myt_taxon_list, t1.query_prot_id,
             count(t1.query_prot_id) / (select count(t.id)
                                        from taxa t, stax st
                                        where t.systematics like concat('%; ', st.taxon_list, '%')
                                              and t.taxon_name not like '%thaliana%'
                                              and st.taxon_list = t1.taxon_list
                                        group by t1.taxon_list) as taxfrac
      from task1 t1
      where t1.taxon_list = 'Stramenopiles'
      group by t1.query_prot_id) myt 
where taxfrac > 0.5

In the last line you can read 'Stramenopiles'. The result of this is a simple count. Now I want to write a bash script that writes the counts not only for Stramenopiles but for all entries in that column - not as a sum, but for every entry separated. I need to combine this with an iterative loop in bash but I never scripted before. Can someone help me with this?

Upvotes: 1

Views: 490

Answers (1)

Olaf Dietsche
Olaf Dietsche

Reputation: 74048

I wouldn't do this in bash at all. If you remove 

where t1.taxon_list = 'Stramenopiles'

you can use a group by myt_taxon_list in your outer select 

select myt_taxon_list, count(myt_taxon_list)
from (select t1.taxon_list as myt_taxon_list, t1.query_prot_id,
             count(t1.query_prot_id) / (select count(t.id)
                                        from taxa t, stax st
                                        where t.systematics like concat('%; ', st.taxon_list, '%')
                                              and t.taxon_name not like '%thaliana%'
                                              and st.taxon_list = t1.taxon_list
                                        group by t1.taxon_list) as taxfrac
      from task1 t1
      group by t1.query_prot_id, t1.taxon_list) myt 
where taxfrac > 0.5
group by myt_taxon_list

which gives you all the taxon_list entries nicely counted.

Upvotes: 1

Related Questions