Search code examples
mysqldatabasegenome

Average repetition of a value on MySQL

I'm working on genome databases with MySQL, and I have to take the average amount of transcripts (entries in each table) for each gene (tagged on its own column, so every transcript for the same gene has the same number). For example: transcript_name chr start end exons gene_name

I've tried with this code, but didn't worked:

mysql> SELECT Avg(COUNT(*) FROM refGeneshg GROUP BY name2);

How can I count how many times each tag appears and the take the average on MySQL?

Solution

  • Following query should work.

    SELECT AVG(count)
FROM
(SELECT COUNT(*) as count 
 FROM refGeneshg
 GROUP BY name2) as counts;