Hi everybody, I 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. Anybody any idea ? Thanks a lot Stefan
-- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-5092 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
