0

I want to see all fsid limit 25 from my table -- I tried this query

Select * 
from btmt 
WHERE Count(fsid) > 1
limit 24

But this throws an error of

Aggregates are not allowed in WHERE clause.

What do I need to alter in order to run this query?

0

you can use something like...

SELECT id, Count(fsid) As cnt FROM btmt GROUP BY 1

...as a subquery (substitute name of your id field for "id") and wrap it up like this...

SELECT b.* FROM btmt b,
(SELECT id, Count(fsid) As cnt FROM btmt GROUP BY 1) As z
WHERE b.id = z.id AND z.cnt > 1
LIMIT 24;

Hard to tell exactly what you're looking for, but that will get you 24 unordered results from the btmt table with fsid value of greater than 1, although it might not be the prettiest way to do so, computationally speaking.

Please clarify if that's not what you're after--hope it helps if so.

EDIT: if you're looking for fsid values 1-25 it would be more like...

SELECT b.*, z.cnt FROM btmt b,
(SELECT id, Count(fsid) As cnt FROM btmt GROUP BY 1) As z
WHERE b.id = z.id AND z.cnt BETWEEN 1 AND 25
ORDER BY z.cnt; --through that in just for the fun of it :-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.