CODEWITHSUNDEEP
pythonsqlalchemy
etangman
etangman

Reputation: 45

SQL Alchemy group_by in Query

I am trying to select a grouped average.

a1_avg = session.query(func.avg(Table_A.a1_value).label('a1_avg'))\
         .filter(between(Table_A.a1_date, '2011-10-01', '2011-10-30'))\
         .group_by(Table_A.a1_group)

I have tried a few different iterations of this query and this is as close as I can get to what I need. I am fairly certain the group_by is creating the issue, but I am unsure how to correctly implement the query using SQLA. The table structure and expected output:

TABLE A
A1_ID | A1_VALUE | A1_DATE    | A1_LOC | A1_GROUP
1     |  5       | 2011-10-05 | 5      |  6      
2     | 15       | 2011-10-14 | 5      |  6
3     |  2       | 2011-10-21 | 6      |  7
4     | 20       | 2011-11-15 | 4      |  8
5     |  6       | 2011-10-27 | 6      |  7

EXPECTED OUTPUT
A1_LOC | A1_GROUP | A1_AVG
5      | 6        | 10
6      | 7        | 4

Upvotes: 1

Views: 1481

Answers (1)

van
van

Reputation: 77012

I would guess that you are just missing the group identifier (a1_group) in the result. Also (given I understand your model correctly), you need to add a group by clause also for a1_loc column:

edit-1: updated the query due to question specificaion

a1_avg = session.query(Table_A.a1_loc, Table_A.a1_group, func.avg(Table_A.a1_value).label('a1_avg'))\
         .filter(between(Table_A.a1_date, '2011-10-01', '2011-10-30'))\
         #.filter(Table_A.a1_id == '12')\ # @note: you do NOT NEED this
         .group_by(Table_A.a1_loc)\ # @note: you NEED this
         .group_by(Table_A.a1_group)

Upvotes: 3

Related Questions