scott wrote:
> 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).

<Clippy> It looks like you're trying to use a relational database! </Clippy>

Seriously, the problem you're running into is that you're using something 
that's only mostly relational, without enforcing the 
one-identical-row-per-table rule. In particular, you're using "group by" to 
eliminate duplicates. But enough theory.

I don't know what database engine you're using, but under MySql, you can say
select distinct expid from exp where eventid=1;
and it'll throw out the duplicate expid's. Then you can say
select count(distinct expid) from exp where eventid=1;
and you'll get just the number of rows.

Of course, the same without the "where" will give you the number of 
experiments, so just divide them.

Other servers use the term "unique" instead of "distinct". Check your 
documentation.

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

You can probably assign the results of the two counts to two variables and 
then return the ratio all in one transaction without too much trouble. I 
don't remember ever coming up with a way of having two "where" statements 
easily in one final result.

Another way might be to do something really messy with outer joins on a 
table so you get something with an eventid column that's either "1" or NULL, 
turn the NULLs into 0's, and then find the AVG(), but it's probably way, way 
easier to do two SQL statements, even if you wind up using temp variables 
and doing the math on the server.

-- 
   Darren New, San Diego CA, USA (PST)
   My fortune cookie said, "You will soon be
   unable to read this, even at arm's length."

Post a reply to this message