convert sqlalchemy query written for sqlite to postgres-CodePudding

Flask app made using sqlite and now converting to postgres. I have the following query to gather the daily sales from multiple stores. Then put the sales into a list to use in a chartjs chart.

    daily_chart = (
        db.session.query(Sales, func.sum(Sales.sales).label("total_sales"))
        .filter(Sales.date >= start_week, Sales.date <= end_week)
        .group_by(Sales.date)
        .order_by(Sales.date)
    )
    values1 = []
    for v in daily_chart:
        values1.append(v.total_sales)

I get the following error.

(psycopg2.errors.GroupingError) column "Sales.id" must appear in the GROUP BY clause or be used in an aggregate function

I tried to use with_entities but don't really know sql and could't find an answer that worked with the func statement.

CodePudding user response：

Try removing Sales from the query statement.