SQLAlchemy: How to filter after aggregation

user_2529619 0
Viewed 2210

How do I filter out all groups with aggregate share totals of 0?

q = session.query(Trades.ticker, func.sum(Trades.shares))
g = q.group_by(Trades.ticker)
f = g.filter(func.sum(Trades.shares) != 0)
result = f.all()

This throws the error on line 3:

... sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) misuse of aggregate: sum() ...

A workaround is to remove the filter and use a list comprehension:

result = [x for x in g.all() if x[1] != 0.0]

But this is very slow compared to filtering a SQL query, because it loads all the data into memory as opposed to only loading the filtered data.

(EDITED to simplify.)

sqlalchemy python
1 Answers

Okay, the answer is to use having(). See related.

q = session.query(Trades.ticker, func.sum(Trades.shares))
g = q.group_by(Trades.ticker)
f = g.having(func.sum(Trades.shares) != 0)
result = f.all()

Nice, simple, and fast.

Writing out the question helped me think it through clearer and helped me find the answer in the documentation. I'm leaving this here in case it helps someone.

(EDITED to reflect revised question.)

user_25296190
Related