Filtering by Count() in SQlite raises an error

[arski]

What I'm trying to do is something like:

User.select(User, fn.Count(Tweet)).join(Tweet).where(fn.Count(Tweet) > 0)

Sadly, in SQlite, this throws an OperationalError: misuse of aggregate: Count() - which upon further investigation on SO turns out to be a specific SQlite issue where filtering by aggregates has to be done inside HAVING clause instead of WHERE - http://stackoverflow.com/questions/648083/sql-error-misuse-of-aggregate - any chance you could look into this please

[coleifer]

Your query is incorrect.

(User
 .select(User, fn.COUNT(Tweet.id).alias('count'))
 .join(Tweet)
 .group_by(User)
 .having(fn.COUNT(Tweet.id) > 0))

[coleifer]

When aggregating, you can only filter on aggregates in the HAVING clause. Additionally, you need to tell the database how to group the rows -- in this case, by user.

[arski]

My bad, I thought I had filtered by SUM or COUNT in a WHERE clause in MySQL before.. but maybe that was an erroneous recollection. thanks for the help!