do a query like
select count(*) from SPECTRUM where SPECTRUM.REVIEW_FLAG ="true" group by
MOLECULE_ID
which gives back a lot of figures, mainly 1 and 2, perhaps 3. What I actually
want is the count how many 1s and 2s (and 3s ...) are in this result set.
What would be the best way to do ?
I found 3 possible solutions, all not working or not good :-)
-Subqueries, probably best, but not possible in MySQL currently.
-Doing a loop over the first result set in my Java code. Possible, but slow.
-I tried a "nested count" query like: select count(count(*)) from SPECTRUM
where SPECTRUM.REVIEW_FLAG ="true" group by count(*),MOLECULE_ID; but this
seems not possible.
If the number of possible counts were small enough, you could group by the count column, and use HAVING to limit the result set to a particular count. Repeat that for each count, and join the statements with UNION.
For more than a few possible counts, though, I'd probably use a temporary table, and do a second select from that.
___/
/
__/
/
____/
Ed Leafe
http://leafe.com/
http://opentech.leafe.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
