SQLAlchemy subquery - average of sums

Question

Hello, is there any way how to write the following SQL statement in SQLAlchemy ORM:

SELECT AVG(a1) FROM (SELECT sum(irterm.n) AS a1 FROM irterm GROUP BY irterm.item_id);

Thank you

Answer 1

Please see SQLAlchemy's tutorial on subqueries.

Answer 2

sums = session.query(func.sum(Irterm.n).label('a1')).group_by(Irterm.item_id)
average = session.query(func.avg(sums.subquery().columns.a1)).scalar()