Group by & count function in sqlalchemy

Question

I want a "group by and count" command in sqlalchemy. How can I do this?

Seems to be a duplicate of http://stackoverflow.com/questions/370174/sqlalchemy-with-count-groupby-and-orderby-using-the-orm — Arnkrishn, Jun 27 '09 at 05:19

score 247 · Accepted Answer · edited Jun 24 '22 at 07:11

247

The documentation on counting says that for group_by queries it is better to use func.count():

from sqlalchemy import func
session.query(Table.column, 
   func.count(Table.column)).group_by(Table.column).all()

edited Jun 24 '22 at 07:11

Ahmad

answered Nov 03 '10 at 10:44

miniwark

31

and here's the full statement for those using `Table.query` property instead of `session.query()`: `Table.query.with_entities(Table.column, func.count(Table.column)).group_by(Table.column).all()` – Jakub Kukul Mar 26 '18 at 23:33
2

@jkukul This should be an answer on its own - I always wondered how to get around this limitation when doing subqueries and wanted to use group_by and count..! – chris-sc Jul 05 '18 at 12:03
1

The edits to this answer render the first sentence kind of meaningless. "Better" than *what*? – Mark Amery Nov 09 '18 at 13:38

score 65 · Answer 2 · answered Jul 05 '18 at 13:58

65

If you are using Table.query property:

from sqlalchemy import func
Table.query.with_entities(Table.column, func.count(Table.column)).group_by(Table.column).all()

If you are using session.query() method (as stated in miniwark's answer):

from sqlalchemy import func
session.query(Table.column, func.count(Table.column)).group_by(Table.column).all()

answered Jul 05 '18 at 13:58

Jakub Kukul

1

fantastic. Question. how can i do this? `count(column_name) as total_count` – Rolly May 20 '21 at 22:32
https://stackoverflow.com/a/9187589/7246111 – Vladimir Yakovenko Jan 07 '22 at 11:07

score 37 · Answer 3 · answered Mar 11 '11 at 17:10

37

You can also count on multiple groups and their intersection:

self.session.query(func.count(Table.column1),Table.column1, Table.column2).group_by(Table.column1, Table.column2).all()

The query above will return counts for all possible combinations of values from both columns.

answered Mar 11 '11 at 17:10

fccoelho

Thanks for the question, while thinking about it I found an answer to a related question of mine. ;-) – fccoelho Mar 11 '11 at 17:11

3 Answers3