SQL Query

[aph]

All,
There is a table with gene_ids ('gid') and clone_ids ('cid'). Each gene only resides on a single clone and each clone may contain multiple genes. How do you find how many genes are on each and every clone? Please provide the SQL.

Thanks in advance.

[gandolf989]

select cid, count(*) from(
select distinct cid, gid from thetablewhereyourddatais)
group by cid
order by cid;

[PAVB]

I probably missed something but at first glance I would do...

select   cid         "Clones", 
         count(*)    "Genes"
from     thetablewhereyourddatais
group by cid
order by cid;

[gandolf989]

I probably missed something but at first glance I would do...

select   cid         "Clones", 
         count(*)    "Genes"
from     thetablewhereyourddatais
group by cid
order by cid;

As long as the cid, gid columns are unique in the table where aph is running the query, then your answer is correct. I just wrote it so that it would not matter if they were unique or not.

[PAVB]

As long as the cid, gid columns are unique in the table where aph is running the query, then your answer is correct. I just wrote it so that it would not matter if they were unique or not.

Reading poster's specs about "Each gene only resides on a single clone..." I supposed data was compliant with the specs. I've to agree if data is a mess your solution might show a closer solution.

[LKBrwn_DBA]

And...you gave him the fish instead of teaching him how to fish!

[PAVB]

And...you gave him the fish instead of teaching him how to fish!

Not my fault, when I got there the guy was already eating a proper Fish and Chips with mushy peas