[해결 방법] ORA-00937: not a single-group group function

👉 기본 환경

- Language: Oracle

 

 

⌨️ 코드

WITH SUB AS (
      SELECT TO_CHAR(DATETIME, 'FMHH24') AS HOUR
        FROM ANIMAL_OUTS
    GROUP BY TO_CHAR(DATETIME, 'FMHH24')
)
  SELECT HOUR, COUNT(*) AS COUNT
    FROM SUB
   WHERE HOUR BETWEEN 9 AND 19
ORDER BY TO_NUMBER(HOUR)
;
 

 

 

🖨️오류

ORA-00937: not a single-group group function

 

 

📡 원인

GROUP BY 절과 SELECT 절 사이에서 집계 함수가 필요한데, 그룹화되지 않은 열에 대해 집계 함수를 사용하지 않음

 

 

📰 해결 방법

WITH SUB AS (
      SELECT TO_CHAR(DATETIME, 'FMHH24') AS HOUR,
             COUNT(*) AS COUNT
        FROM ANIMAL_OUTS
    GROUP BY TO_CHAR(DATETIME, 'FMHH24')
)
  SELECT HOUR, COUNT
    FROM SUB
   WHERE HOUR BETWEEN 9 AND 19
ORDER BY TO_NUMBER(HOUR)
;
 

SELECT절에 집계함수 COUNT 추가