SQLAlchemy - column must appear in the GROUP BY clause or be used in an aggregate function-CodePudding

I am using sqlalchemy with postgresql,

Tables

Shape[id, name, timestamp, user_id]#user_id referring id column in user table
User[id, name]

this query -

        query1 = self.session.query(Shape.timestamp, Shape.name, User.id,
            extract('day', Shape.timestamp).label('day'),
            extract('hour', Shape.timestamp).label('hour'),
            func.count(Shape.id).label("total"),
            )\
        .join(User, User.id==Shape.user_id)\
        .filter(Shape.timestamp.between(from_datetime, to_datetime))\
        .group_by(Shape.user_id)\
        .group_by('hour')\
        .all()

this works well in sqlite3 sqlalchemy, but it is not working in postgresql sqlalchemy

I got this error -> (psycopg2.errors.GroupingError) column "Shape.timestamp" must appear in the GROUP BY clause or be used in an aggregate function

I need to group only by the user_id and the hour in the timestamp, where the Shape.timestamp is the DateTime python object

but, the error saying to add the Shape.timestamp in the group_by function also, If i add the Shape.timestamp in the group_by, then it shows all the records

If i need to use some function on other columns, then how i will get the other column actual data, is there any way to get the column data as it is without adding in group_by or using some function

How to solve this

CodePudding user response：

This is a basic SQL issue, what if in your group, there is several timestamp values ?

You either need to use an aggregator function (COUNT, MIN, MAX, AVG) or specify it in your GROUP BY.

NB. SQLite allows ungrouped columns in GROUP BY, in which case "it is evaluated against a single arbitrarily chosen row from within the group." (SQLite doc section 2.4)

CodePudding user response：

Try changing the 9 line ->

query1 = self.session.query(Shape.timestamp, Shape.name, User.id,
            extract('day', Shape.timestamp).label('day'),
            extract('hour', Shape.timestamp).label('hour'),
            func.count(Shape.id).label("total"),
            )\
        .join(User, User.id==Shape.user_id)\
        .filter(Shape.timestamp.between(from_datetime, to_datetime))\
        .group_by(Shape.user_id)\
        .group_by(extract('hour', Shape.timestamp))\
        .all()