CODEWITHSUNDEEP
pythonmysqlsqlalchemy
steve
steve

Reputation: 1418

How to use sum and order by in SQLAlchemy query

I'm trying to return a sum row from my table and order with the sum result.

My sql like this:

self.db_user_online.query(
    MeleeGameData,
    func.sum(MeleeGameData.core_data).label("ct")
).\
    group_by(MeleeGameData.ccid).\
    filter_by(mid=self.cycle_id).\
    order_by("ct desc").\
    all()

Debug echo sql:

SELECT fiels..., sum(t_act_melee_game_data.core_data) AS ct
FROM t_act_melee_game_data
WHERE t_act_melee_game_data.mid = %s
GROUP BY t_act_melee_game_data.ccid
ORDER BY ct DESC

But it's can't work..

Upvotes: 9

Views: 7287

Answers (2)

lee
lee

Reputation: 61

you could try

self.db_user_online.query(
    MeleeGameData,
    func.sum(MeleeGameData.core_data).label("ct")
) \
    .group_by(MeleeGameData.ccid) \
    .filter_by(mid=self.cycle_id) \
    .order_by(desc("ct")) \
    .all()

Upvotes: 2

Joost Döbken
Joost Döbken

Reputation: 4025

Starting with your SQL code; I suppose you want to group by every ccid and then sum the corresponding data (I took out the filter for simplicity):

SELECT t_act_melee_game_data.ccid,
       sum(t_act_melee_game_data.core_data) AS ct
FROM t_act_melee_game_data
GROUP BY t_act_melee_game_data.ccid
ORDER BY sum(t_act_melee_game_data.core_data) DESC

Construct in SQLAlchemy;

self.db_user_online.query(
    MeleeGameData.ccid,
    func.sum(MeleeGameData.core_data).label("ct")
).\
    group_by(MeleeGameData.ccid).\
    order_by(func.sum(MeleeGameData.core_data).desc()).\
    all()

Upvotes: 7

Related Questions