I told you these would be coming :-)

OK so I have a table like this (there are other columns, but these are the 
important 2):

ExpID  EventID
1      1
1      3
1      3
1      6
2      1
2      2
2      7
3      2
3      2
3      3
3      4
...

Where ExpID is the number of an experiment and EventID signifies some event 
has happened during that experiment.  Note that events can happen more than 
once for each experiment.

My goal is to calculate, for example, the % of experiments that have had a 
certain EventID happen one or more times.

I have got this far (for counting experiments where EventID 1 happened):

SELECT ExpID FROM Table WHERE EventID=1 GROUP BY ExpID

This gives me a list of ExpIDs where eventID 1 has happened.  I can manually 
count the rows, but how do I write the SQL to just return the number of 
rows?  I've tried all sorts of COUNT but it won't work (either returns 1 or 
an error).

Also is there any way to calculate the % of experiments (result from that 
query above divided by the total number of experiments) in one SQL 
statement, or do I have to do that by hand after issuing two statements?

Post a reply to this message