count unique rows in sql-alchemy

Question

I am new to sql alchemy and I would like to count unique rows in my table and output the unique rows together with the number of copies for that row. Lets assume I have a table like this

Table A
--------------
id
address

now I want to get all rows of this table but for rows with the same adress I want to get only one row (doesn't matter which id). I also want to know how many ids are at a particular address. So if there are two people living at the same address "main street" (lets say id=4 and id =12) I would like to get an output like this ("main street", 2),

Here is my starting attempt

query = models.A.query
query = query.add_columns(func.count(models.A.address)).all()

this however, gives me the total number of rows in that table. So I guess func.count is the wrong function? thanks in advance carl

van · Accepted Answer · 2015-08-06 19:50:01Z

5

count is the right function, but you need to specify groups of what you would like to count. Below should do it:

q = (session.query(A.address, func.count(A.id).label("# people"))
    .group_by(A.address)
     ).all()

answered Aug 6, 2015 at 19:50

van

77.6k13 gold badges174 silver badges179 bronze badges

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

1 Comment

tista3 Over a year ago

Importing func is as simple as this: from sqlalchemy import func

Collectives™ on Stack Overflow

count unique rows in sql-alchemy

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related