> I don't know what database engine you're using,

MS SQL CE, I think you pointed me to that one :-)

> select count(distinct expid) from exp where eventid=1;
> and you'll get just the number of rows.
>
> Other servers use the term "unique" instead of "distinct". Check your 
> documentation.

Hmmm, SELECT DISTINCT expid works, but SELECT COUNT(DISTINCT expid) throws a 
parse error at "DISTINCT" from SQL CE at runtime.  Actually I got this 
before when I was semi-experimenting the other night and couldn't work out 
why that didn't work.

<googling>

OK it seems that SQL CE doesn't support DISTINCT inside COUNT, great.  They 
suggest workarounds, and this works:

SELECT COUNT(*) FROM (SELECT DISTINCT expid FROM exp WHERE eventid=1) AS T

Finally!

So I take it using SELECT DISTINCT X ... is better than using SELECT X ... 
GROUP BY X for what I want to do?

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

OK thanks for the tips, I think I will try and get the basic queries working 
neatly and fast first :-)

Post a reply to this message