CODEWITHSUNDEEP
sqlalchemyflask-sqlalchemycx-oracle
Marcelo Ceolin
Marcelo Ceolin

Reputation: 43

ORA-00979 when using group_by with sqlalchemy

I have a query, which makes a count of the total tasks based on the month of creation, however sqlalchemy is bringing me the following error:

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00979

This is my sqlalchemy query:

tasks_by_month = (
        db.session.query(
            func.to_char(Task.creation_time, "MM").label("month"),
            func.count(Task.id).label("count"),
        )
        .group_by(func.to_char(Task.creation_time, "MM"))
        .order_by(text("month asc"))
        .all()
    )

This is the query in sqldeveloper:

SELECT
    TO_CHAR(TA.CREATION_TIME, 'MM') AS MONTH,
    COUNT(TA.ID)
FROM
    TASKS TA
GROUP BY
    TO_CHAR(TA.CREATION_TIME, 'MM')
ORDER BY
    MONTH

I am expecting the following result

enter image description here

Thanks for help

Upvotes: 1

Views: 104

Answers (1)

Marcelo Ceolin
Marcelo Ceolin

Reputation: 43

I managed to solve the problem by changing func.to_char(Task.creation_time, "MM").Label ("month") to extract('month', Task.creation_time).label("month").

tasks_by_month = (
        db.session.query(
            extract('month', Task.creation_time).label("month"),
            func.count(Task.id).label("count"),
        )
        .group_by(extract('month', Task.creation_time))
        .order_by(text("month asc"))
        .all()
    )

Thanks :)

Upvotes: 1

Related Questions