CODEWITHSUNDEEP
pythonsqlsqlalchemy
azdonald
azdonald

Reputation: 336

Using func.max and func.count in one query

I have two tables:

class Shops(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(255))
    order = db.relationship('Order', backref = 'purchased', lazy = 'dynamic')

class Order(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    date = db.Column(db.DateTime)
    shop = db.Column(db.Integer, ForeignKey('shop.id'))

I want to get the highest selling shop based on order, like so:

query = db.session.query(Shops.name.label('TopShop'),Order,func.max(func.count(Order.shop)))

Which gives the following error:

ProgrammingError: (ProgrammingError) (1111, u'Invalid use of group function')

Any pointers in the right direction please?

Upvotes: 1

Views: 481

Answers (1)

van
van

Reputation: 76992

Top shop(s) by number of orders:

subq = (session.query(Order.shop, func.count(Order.id).label("num_orders")).
        group_by(Order.shop)).subquery()
q = session.query(Shops).join(subq, Shops.id == subq.c.shop).order_by(subq.c.num_orders.desc())

# top 1
print q.first()
# top 5
print q[:5]

Upvotes: 1

Related Questions