MySQL DBI frequency count

[Ronaldo]

Hi,

I am now having trouble with generating a query in Perl DBI to take data from a database, calculate a frequency and insert it into another table in MySQL; I am still just at the start in learning both MySQL and Perl, and I am not sure if I have to post on the MySQL or the Perl help pages, but I think it is more of a MySQL question…if you have any advice on how to best go about dividing the two counts retrieved from the two select statements to get a frequency could you please help me?

My database is constructed with different tables connected to each other through foreign keys. I used ENGINE=INNODB for constructing the database, and MySQL version 5.1.

These are the two select statements:
1) #count number of alleles entered for each variation:
my $sth =$dbh->prepare("SELECT count(allele_id)
from allele inner join genotype using (allele_id)
group by allele.variation_id");
$sth->execute();
my ($allele_count);
while ( ($allele_count) = $sth->fetchrow_array()) {
print "$allele_count minor alleles\n";
}
$sth->finish;

2)#Count the number of samples that have alternate (minor) allele:
my $sth =$dbh->prepare("SELECT count(allele_id)
from allele inner join genotype using (allele_id)
where allele.reference=1
group by allele.variation_id");
$sth->execute();
my ($min_allele_count);
while ( ($min_allele_count) = $sth->fetchrow_array()) {
print "$min_allele_count minor alleles\n";
}
$sth->finish;

I tried dividing the two sleect statements (giving counts), but this is the error message I get:
"Argument "SELECT allele.variation_id, count(genotype.allele_id) as..." isn't numeric in division (/) at maf.pl line 16.
Illegal division by zero at maf.pl line 16"

Thanx VERY much for any advice

[UserName]

SELECT count(allele_id)
from allele inner join genotype using (allele_id)
group by allele.variation_id

SELECT count(allele_id)
from allele inner join genotype using (allele_id)
where allele.reference=1
group by allele.variation_id

each of these queries will produce multiple counts -- one per variation

are you looking to divide the matching ones?

or were you thinking that there would be only one count out of each query?

[Torque]

Yes, it looks like a MySQL problem to me too (although I suspect that you could solve it at the application level too).

If you agree that you want to solve it in MySQL, instead of all this PERL stuff just give us some CREATE and INSERT 'DLLs' for all the relevant tables as well as an indication of the desired result.

[Ronaldo]

Hello UserName,

Yes the result is many counts per variation, and you understood perfectly what I am trying to do: divide the matching ones (by variation_id). In perl I can get an array and divide each element by eachother, but I was wondering whether there was a better/easier way to do this through MySQL?

[Ronaldo]

Hey Torque,

If MySQL DBI has an easy way to to this I would prefer to do it through this, but I am not sure how to do it...I had created the database using a .sql script, and inserted values using a Perl script to parse a file. What I would like is just a frequency calculated from a count of alleles in one table for each variation, divided by a count of alleles where reference=1 in the same table for each variation, so something like:

varioation, allele_total, allele_minor, frequency_of_allele_minor

1, 120, 40, 0.33
2, 222, 32, 0.14

Hope this helps...

[UserName]

Originally Posted by Ronaldo

I was wondering whether there was a better/easier way to do this through MySQL?

you betcha

SELECT allele.variation_id
     , COUNT(*) AS total
     , COUNT(CASE WHEN allele.reference = 1
                  THEN 1 
                  ELSE NULL END) AS alternates 
  FROM allele 
INNER 
  JOIN genotype 
    ON genotype.allele_id = allele.allele_id 
GROUP 
    BY allele.variation_id

[Ronaldo]

Originally Posted by UserName

you betcha

SELECT allele.variation_id
     , COUNT(*) AS total
     , COUNT(CASE WHEN allele.reference = 1
                  THEN 1 
                  ELSE NULL END) AS alternates 
  FROM allele 
INNER 
  JOIN genotype 
    ON genotype.allele_id = allele.allele_id 
GROUP 
    BY allele.variation_id

Dear UserName,

IT WORKS! THANK YOU SO MUCH! I was approaching it all wrong...makes me realise how much I still have to learn about MySQL!! Thank you so much for the help!