Multiple group_by counts on single table using SQLAlchemy

Question

I'm working with SQLAlchemy (1.4/2.0 Transitional style) to access a PostgreSQL database. I am using group_by to count the number of unique names ordered by the count. What I need to do is then apply additional group_by to count the same thing, but with different conditions. For example, if I have this User object

class User(Base):
    __tablename__ = "user"

    user_id = Column(Integer, primary_key=True)
    name = Column(String)
    city = Column(String)

I can get the top 100 most popular User names and the counts of those with that name using:

stmt = select(User.name, func.count(User.user_id).label("total")) \
       .group_by(User.name) \
       .order_by(desc('total')) \
       .limit(100)

What I need to do is to for those 100 most popular names, find how many have those names where city == "New York", "Chicago" & "Los Angeles". I'd like to get the data back something like:

name: "Charles Smith", total:526, NY:28, Chi:17, LA:23

Any idea how to do this?

Belayer · Accepted Answer · 2021-08-12 00:03:46Z

1

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.

answered Aug 12, 2021 at 0:03

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

markand Over a year ago

Thank you! It is important to note that with the text() function, you can just pass in SQL as is. So I just needed to declare a string with your SQL, pass it to session.execute(text(query_string)). Works perfectly. Thank you!

Belayer Over a year ago

Cool and thanks! If I ever try SQLAlchemy I will try to remember this. If I do not remember maybe I will find this question/answer searching SO. I think it could be pretty cool to find an answer to my question in a question I provided an answer for.

Collectives™ on Stack Overflow

Multiple group_by counts on single table using SQLAlchemy

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related