9QM7cnsE

1CREATE TABLE IF NOT EXISTS trap.experiment_len_comb (
2	experiment_id,
3	comb_id,
4	transcripts,
5	distinct_transcripts
6) PRIMARY KEY (experiment_id, comb_id) ENGINE 'InnoDB'
7SELECT trap.cell_snapshot.experiment_id AS experiment_id,
8       superfamily.comb.comb_id AS comb_id,
9       count(trap.cell_snapshot.cell_transcript_id) AS transcripts,
10       count(distinct(trap.cell_snapshot.cell_transcript_id)) AS distinct_transcripts
11FROM trap.cell_snapshot 
12INNER JOIN trap.experiment ON trap.cell_snapshot.experiment_id = trap.experiment.experiment_id
13INNER JOIN trap.id_mapping ON trap.cell_snapshot.gene_id = trap.id_mapping.entrez
14INNER JOIN superfamily.comb ON superfamily.comb.protein = trap.id_mapping.protein
15GROUP BY trap.cell_snapshot.experiment_id, superfamily.comb.comb_id
16ORDER BY comb_id, transcripts DESC;