How to use sum and order by in SQLAlchemy query

Question

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..

Do you know how to write the raw SQL that you want? – univerio Mar 17 '16 at 06:18 — univerio, Mar 17 '16 at 06:18

score 7 · Accepted Answer · answered Mar 17 '16 at 08:18

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

score 2 · Answer 2 · edited Dec 10 '20 at 10:34

2

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

edited Dec 10 '20 at 10:34

Mazyod

22,319
10
92
157

answered Nov 22 '19 at 03:12

lee

61
5

How to use sum and order by in SQLAlchemy query

2 Answers2