scott wrote:
>> OK, what *precisely* are you trying to count?
>>
>> 2. The number of experiments where event X occurred at least once?
> 
> This one ^ the number of experiments where event X occurred at least once.
> 
> In my first post I said what I had got already:
> 
> SELECT ExpID FROM Table WHERE EventID=1 GROUP BY ExpID
> 
> This gives me a list of unique ExpIDs where event X occurred at least 
> once. I simply want it to return the count of those returned rows rather 
> than the rows themselves, so I just want a single number returned.

In that case, I think you can just do

   SELECT count(ExpID) FROM Table WHERE EventID=1 GROUP BY ExpID;

I could be wrong on that though...

> This 
> will then be divided by the total number of experiments in the database, 
> to give a % of experiments where event X occurred one or more times.

Right. OK.

>> Well, you could always store the SQL in the database itself! ;-)
> 
> Cunning :-)  Although it would need to be a separate database, as 
> currently I'm keeping the option open for the user to specify database 
> file to create and use, so if they want they can keep several separate 
> databases and my app just works on one of them at a time.

Ah, I see.

Apparently the way the "big boys" do it is with "prepaired statements". 
Basically the database contains something like

   SELECT count(*) FROM Table WHERE EventID=%X% GROUP BY ExpID;

and each time you use the statement, you get to say what %X% should be. 
(This means the database can parse the text of the statement, build a 
query plan, optimise it and so forth just once - when the statement is 
"prepaired" - and then each time you execute it, it just reuses the 
execution plan with the new variable value.) Of course, that's more of 
an issue if you're trying to run Amazon.com or something. ;-)

Post a reply to this message