0

I have the following SQLAlchemy query that joins two tables, selects a number of exsisting columns and calcultes two more based on sum and count functions. When testing on a local SQLite database the query works just fine, but when I run it on my Heroku server which uses a PostgreSQL database I get the following error:

Column Book.firstname_sold must appear in the GROUP BY clause or be used in an aggregate function

The query looks like this:

book_list = db.session.query(Receipt.id,
                               Book.firstname_sold,
                               Book.lastname_sold,
                               Book.email_sold,
                               Book.sold_date,
                               Book.street_sold,
                               Book.number_sold,
                               Book.postalcode_sold,
                               Book.city_vsold,
                               func.sum(func.cast(func.replace(Book.price_sold, ",", "."), Integer)).label("price"),
                               func.count(Book.id).label("count_books"))\
    .join(Book).filter(Receipt.user_id == current_user.id).group_by(Receipt.id)

How would I rewrite this query so that it works both on SQLite and PostgreSQL?

Stefan
  • 61
  • 1
  • 1
  • 9
  • 1
    Do all books in a group have the same values for email_sold etc.? If not, how do you think SQLite chooses which row it takes the values from in order to produce the group row? In other words the query may work as in does not produce an error, but what does the result mean? PostgreSQL on the other hand is following the SQL standard here and does not allow this query. – Ilja Everilä Jun 01 '20 at 21:38
  • Here's the difference in behaviour explained: https://stackoverflow.com/questions/55419442/how-to-fetch-rows-with-max-update-datetime-using-group-by-and-having-with-sqlalc – Ilja Everilä Jun 01 '20 at 21:48
  • Yes all books have the same value for all the fields except the price which is the one I'm trying to sum – Stefan Jun 02 '20 at 16:09

0 Answers0