CODEWITHSUNDEEP
pythonsqloracle-databaseflasksqlalchemy
Kim
Kim

Reputation: 19

ORA-00979 in sqlalchemy when using group_by

I have a query, which makes an average of the power based on the date, however sqlalchemy is bringing me the following error:

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00979: not a GROUP BY expression

This is my sqlalchemy query:

result = db.session.query(
func.to_char(myTable.datetime, 'DD.MM.YY'), func.avg(myTable.power))
.group_by(func.to_char(myTable.datetime, 'DD.MM.YY'))
.order_by(func.to_char(myTable.datetime, 'DD.MM.YY'))
.all()

This is the query in sqldeveloper, which works there, but not in my flask app:

SELECT 
  to_char(myTable.datetime, 'DD.MM.YY'), avg(myTable.power)
FROM 
  myTable 
GROUP BY 
  to_char(myTable.datetime, 'DD.MM.YY') 
ORDER BY
  to_char(myTable.datetime, 'DD.MM.YY');

When I use extract, like in this post, it works, but I want to get a date as string.

Upvotes: 0

Views: 172

Answers (1)

Kim
Kim

Reputation: 19

Thanks to the comment of MT0 the TRUNC function works for me. My sqlalchemy query now looks like this:

result = db.session.query(
func.to_char(func.trunc(myTable.datetime)), func.avg(myTable.power))
.group_by(func.trunc(myTable.datetime))
.order_by(func.trunc(myTable.datetime))
.all()

Upvotes: 1

Related Questions