scott wrote:

> 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).

I don't have an SQL engine in front of me, but I would have expected

   SELECT ExpID, COUNT(*) FROM Table WHERE EventID=1 GUOP BY ExpID;

to do what you want.

> 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?

Again, off the top of my head, I would suggest that

   SELECT ExpID, COUNT(*) / Total
   FROM Table, (SELECT COUNT(*) as Total FROM Table2)
   GOUP BY ExpID;

or similar ought to do it. (As you can see, you can run a query inside 
another query to generate the total.)

Post a reply to this message