Group by & Count Function in SQLalchemy

Group by & count function in sqlalchemy

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()

group by and count in sqlalchemy

You need to use a subquery to count the cities per country and than select the max from count of the subquery:

subq = select(hotel.c.country,
              hotel.c.city,
              func.count(hotel.c.city)
              .label('count'))\
              .group_by(hotel.c.country,
                        hotel.c.city)\
              .subquery()
stmt = select(subq.c.city,
              subq.c.count)\
              .having(func.max(subq.c.count))\
              .group_by(subq.c.country)
print(session.execute(stmt).all())

SQLAlchemy with count, group_by and order_by using the ORM

I've found the best way to do this. Simply supply a from_statement instead of a filter_by or some such. Like so:

meta.Session.query(Location).from_statement(query).all()

sqlalchemy group_by and count

You are right - you cannot do it using query_property because it implicitely selects the whole object (all attributes of it), so adding the group_by would not produce the desired result because undesired columns are included in the non-aggregate part of the query.

However you can simply use a db_session.query([Person.country, ...]).group_by(...). So all you need to do is to add db_session property to the Person class along with query and then use it to create a desired query explicitely:

class Person(object):
    session = db_session
    query = db_session.query_property()
    ....

How to access count value as dict/property in sqlalchemy with group_by?

Found the answer to my question after re-reading the official ORM tutorial

All I need to do is to set a label for the column for which there's no corresponding property in my class.

So, instead of doing this:

servers = session.query(func.count(Server.type), Server.type).group_by(Server.type).all()

I should do that:

servers = session.query(func.count(Server.type).label('total_count'), Server.type).group_by(Server.type).all()

Then I can access the total_count property (provided by the label function):

for server in servers:
    print(server.type, server.total_count)

Multiple group_by counts on single table using SQLAlchemy

Is straight SQL can use the FILTER clause on count to get the individual city counts with standard count to for the total: See example here

select  name, total, ny, chi, la 
  from ( select name 
              , count(*) total 
              , COUNT(*) FILTER (WHERE city='Chicago')     chi
              , COUNT(*) FILTER (WHERE city='Los Angeles') la
              , COUNT(*) FILTER (WHERE city='New York')    ny  
           from users 
          group by   name            
       ) sq;

I leave it to you to convert to SQLAlchemy; since I can read it to understand, but am not comfortable attempting writing it.

---

Related Topics