CODEWITHSUNDEEP
sqloracle-databasesql-order-byaggregate-functions
tychopycho
tychopycho

Reputation: 41

How to fix this query?

I'm trying to select the tickets that are pending each day of the past week.I have this query and get this error: ORA-00935: group function is nested too deeply

SELECT TO_CHAR(UPDATED_AT, 'day') AS WEEK_DAY, COUNT(ID) AS UPDATED_AT
  FROM freshdesk_api
 WHERE UPDATED_AT >= TRUNC(SYSDATE, 'iw')
   AND UPDATED_AT < TRUNC(SYSDATE, 'iw') + 7
   AND STATUS IN (3)
 GROUP BY TO_CHAR(UPDATED_AT, 'day')
 ORDER BY MIN(UPDATED_AT)

Upvotes: 0

Views: 71

Answers (3)

GMB
GMB

Reputation: 222582

Presumably, you want to order rows by the day of the week rather than by the count of rows per day.

However, your are aliasing the COUNT() with the same name as an date column, which causes ambiguity. Use a different alias - UPDATED_AT does not really make sense for a count anywayy:

SELECT TO_CHAR(UPDATED_AT, 'day') AS WEEK_DAY, COUNT(*) AS CNT --> here
  FROM freshdesk_api
 WHERE UPDATED_AT >= TRUNC(SYSDATE, 'iw')
   AND UPDATED_AT < TRUNC(SYSDATE, 'iw') + 7
   AND STATUS = 3
 GROUP BY TO_CHAR(UPDATED_AT, 'day')
 ORDER BY MIN(UPDATED_AT)

Or, if you really want that particular alias in the resultset, do prefix the column name in MIN() with the table name (or alias) to remove the ambiguity:

SELECT TO_CHAR(UPDATED_AT, 'day') AS WEEK_DAY, COUNT(*) AS UPDATED_AT
  FROM freshdesk_api f                     --> table alias
 WHERE UPDATED_AT >= TRUNC(SYSDATE, 'iw')
   AND UPDATED_AT < TRUNC(SYSDATE, 'iw') + 7
   AND STATUS = 3
 GROUP BY TO_CHAR(UPDATED_AT, 'day')
 ORDER BY MIN(f.UPDATED_AT)                --> prefixed column name

Notes:

  • assuming that id is not nullable, count(*) is equivalent to count(id), and more efficient

  • STATUS IN (3) is simpler phrased STATUS = 3

Upvotes: 0

Popeye
Popeye

Reputation: 35920

You are facing an issue with ORDER BY as mentioned in the comment and in other answers.

Try to use the following technique to order the result from Monday to Friday.

SELECT TO_CHAR(UPDATED_AT, 'day') AS WEEK_DAY, 
       COUNT(ID) AS UPDATED_AT, 
       MIN(UPDATED_AT) AS M_UPDATE_DATE -- ADDED THIS AND GAVE IT ALIAS
  FROM freshdesk_api
 WHERE UPDATED_AT >= TRUNC(SYSDATE, 'iw')
   AND UPDATED_AT < TRUNC(SYSDATE, 'iw') + 7
   AND STATUS IN (3)
 GROUP BY TO_CHAR(UPDATED_AT, 'day')
 ORDER BY M_UPDATE_DATE; -- USED ALIAS NAME HERE

Upvotes: 0

Denver
Denver

Reputation: 21

The issue is with the order by clause. I am not sure why you are using MIN(UPDATED_AT) but look into it to resolve your issue. If you are looking to sort by ascending order just use

ORDER BY UPDATED_AT ASC

Upvotes: 1

Related Questions