ORA-30076: invalid extract field for extract source
상황
프로그래머스 입양 시각 구하기(1) 풀이 중 EXTRACT로 HOUR를 추출할려고 써보는데 해당 에러 발생
SELECT EXTRACT(HOUR FROM DATETIME)
FROM ANIMAL_OUTS
원인
데이터타입이 안맞아서 나는 오류
EXTRACT 함수를 사용할때, 2가지 데이터타입을 받을 수 있는데 하나는 DATE 다른 하나는 TIMESTAMP 1)DATE(DATETIME) : YEAR, MONTH, DAY 2)TIMESTAMP : HOUR, MINUTE, SECOND 등등
※DATETIME 데이터타입 DATE + TIMESTAMP = DATETIME DATETIME 자체가 DATE 타입과 TIMESTAMP을 합쳐 놓은 데이터타입
SELECT SYSDATE,
SYSTIMESTAMP,
EXTRACT (YEAR FROM SYSDATE),
EXTRACT (MONTH FROM SYSDATE),
EXTRACT (DAY FROM SYSDATE),
EXTRACT (HOUR FROM SYSTIMESTAMP),
EXTRACT (MINUTE FROM SYSTIMESTAMP),
EXTRACT (SECOND FROM SYSTIMESTAMP)
FROM DUAL
해결
CAST()로 TIMESTAMP형으로 파싱해주면 된다
SELECT EXTRACT(HOUR FROM CAST(DATETIME AS TIMESTAMP)) HOUR
FROM ANIMAL_OUTS
GROUP BY + 서브쿼리로 이용하여, 여러 날짜(DAY)의 HOUR를 그루핑해서 조회할려다가 에러 발생
SELECT HOUR, DATETIME, COUNT(DATETIME) AS COUNT -- FM 공백제거, HH24 시간만 24시제로 표시
FROM (
SELECT TO_CHAR(DATETIME,'FMHH24') AS HOUR, DATETIME
FROM ANIMAL_OUTS
**GROUP BY DATETIME** -- ORA-00937: not a single-group group function
)
ORDER BY DATETIME
원인
GROUP BY 형식을 잘몰라서 생기는 오류 GROUP BY에 지정한 컬럼과 SELECT의 지정한 컬럼이 같아야한다. 한쪽에 컬럼 세팅이 빠져서 생기는 오류 집계함수를 제외하면 컬럼명을 양 쪽에 모두 공유해야한다
해결
위에서는 별칭인 HOUR로는 연계가 안되기 때문에 컬럼을 직접적으로 선언하던가 또는 DATETIME으로 별칭을 짜면 해결 가능
SELECT HOUR, DATETIME -- COUNT(DATETIME) AS COUNT를 지우면 ORA-00937 해결
FROM (
SELECT TO_CHAR(DATETIME,'FMHH24') AS HOUR, DATETIME
FROM ANIMAL_OUTS
GROUP BY DATETIME
)
ORDER BY DATETIME
AND TWIN1.BIRTH_YMD = TWIN2.BIRTH_YMD -- 생년월일이 같을 조건
) TWIN
WHERE EMP.EMP_NO = FAM.EMP_NO
AND -- --> ★★★ 현재 재직자만 추출 되도록 조건을 넣으세요.
AND FAM.REL_TYPE_CD= 'A27'
AND -- --> ★★★ 2012년 1월 1일 부터 2015년 12월 31일 생년월일 조건을 넣으세요.
AND EMP.EMP_NO = FAMR.EMP_NO (+)
AND FAMR.REL_TYPE_CD (+) IN ('A02','A18')
AND EMP.EMP_NO = TWIN.EMP_NO (+)
AND FAM.FAM_NM = TWIN.TWIN1 (+)
;
조건 brainstorming 재직중 : RETIRE 99/12/31 조건 자녀 생년월일 2012.01.01~2015.12.31 : BETWEEN 20120101 AND 20151231 쌍둥이의 경우 두명 모두 추출 : FAM_C SELF JOIN FAM_NM을 F1,F2 따로 조회 및 조건에 NM <>추가 쌍둥이 여부 표시 'Y' : 리터럴 작성 사내부부의 경우, 상대 배우자의 직원번호 추출 : ? 조회 목록 : EMP_NO, EMP_NM, CASE(SPOUSE_EMP_NO 사내부부일때,일반일때), CHILD_NM, CHILD_GENDER, CHILD_BIRTH_YMD, TWIN_YN (직원번호 / 직원성명 / 배우자성명 (사내부부일 때만) / 배우자직원번호 / 자녀성명 / 자녀성별 / 자녀생년월일 / 쌍생아여부)
--- 추출조건1
--- 현재 재직중인 직원의 생년월일이 2012년 1월 1일 부터 2015년 12월 31일 인 자녀 모두추출
SELECT *
FROM FAM_C
WHERE REL_TYPE_CD = 'A27'
AND BIRTH_YMD BETWEEN 20120101 AND 20151231
;
--- 추출조건2
--- 쌍둥이의 경우 두명 모두 추출하고, 쌍둥이 여부에 'Y' 표시 요망
SELECT F1.EMP_NO 직원번호, F1.FAM_NM 쌍둥이1, F2.FAM_NM 쌍둥이2, 'Y' "쌍둥이 여부"
FROM FAM_C F1
JOIN FAM_C F2 ON(F1.EMP_NO = F2. EMP_NO)
AND F1.FAM_NM <> F2.FAM_NM
AND F1.REL_TYPE_CD = 'A27'
AND F1.REL_TYPE_CD = F2.REL_TYPE_CD
AND F1.BIRTH_YMD = F2.BIRTH_YMD
;
--- 추출조건1 + 추출조건2
--- 현재 재직중인 직원의 생년월일이 2012년 1월 1일 부터 2015년 12월 31일 인 자녀 모두추출
--- 쌍둥이의 경우 두명 모두 추출하고, 쌍둥이 여부에 'Y' 표시 요망
SELECT *
FROM FAM_C
WHERE REL_TYPE_CD = 'A27'
AND BIRTH_YMD BETWEEN 20120101 AND 20151231
;
SELECT F1.EMP_NO 직원번호, F1.FAM_NM 쌍둥이1, F2.FAM_NM 쌍둥이2, 'Y' "쌍둥이 여부"
FROM FAM_C F1
JOIN FAM_C F2 ON(F1.EMP_NO = F2. EMP_NO)
AND F1.FAM_NM <> F2.FAM_NM
AND F1.REL_TYPE_CD = 'A27'
AND F1.REL_TYPE_CD = F2.REL_TYPE_CD
AND F1.BIRTH_YMD = F2.BIRTH_YMD
;
-- FROM절의 AND조건들을 WHERE절로 돌려서 넣으면 뭐가 다를까?
SELECT F1.EMP_NO 직원번호, F1.FAM_NM 쌍둥이1, F2.FAM_NM 쌍둥이2, 'Y' "쌍둥이 여부"
FROM FAM_C F1
JOIN FAM_C F2 ON(F1.EMP_NO = F2. EMP_NO)
WHERE F1.FAM_NM <> F2.FAM_NM
AND F1.REL_TYPE_CD = 'A27'
AND F1.REL_TYPE_CD = F2.REL_TYPE_CD
AND F1.BIRTH_YMD = F2.BIRTH_YMD
;
2) LISTAGG를 활용하여 직원번호가 10001483 인 자녀의 성명이 한줄로 추출 되도록 SQL을 작성해 보세요
--3-2) 현재 날짜 기준 재직중인 "이씨 성＂을 가진 직원의 자녀명수가 몇 명인지 리스트를 만드세요.
--힌트: substr, count(*)를 사용
2.문장에서 쿼리 조건 리스트화
-- 재직중, 현재 날짜 기준, 이씨성, 자녀수
3.각 조건마다 예상되는 쿼리 짜기
-- 재직중 : RETIRE 99/12/31 조건
-- 현재 날짜 기준
-- 이씨성 : LIKE '이%'
-- 자녀수 : COUNT()
3-2) 현재 날짜 기준 재직중인 “이씨 성＂을 가진 직원의 자녀명수가 몇 명인지 리스트를 만드세요.
힌트: substr , count(*)를 사용
-- 재직중 : RETIRE 99/12/31 조건
-- 현재 날짜 기준
-- 이씨성 : LIKE '이%'
-- 자녀수
-- COUNT + LIKE
SELECT E.EMP_NM 직원명, COUNT(*)
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND E.EMP_NM LIKE '이%'
GROUP BY E.EMP_NM
;
-- COUNT(SUBSTR)+ LIKE
SELECT E.EMP_NM, COUNT(SUBSTR(E.EMP_NM,2,4)) AS "자녀명수"
FROM EMP_C E
JOIN FAM_C F USING(EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND E.EMP_NM LIKE '이%'
GROUP BY E.EMP_NM
;
-- COUNT + SUBSTR
SELECT E.EMP_NM, COUNT(*) AS "자녀명수"
FROM EMP_C E
JOIN FAM_C F USING(EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND SUBSTR(E.EMP_NM, 1,1) = '이'
GROUP BY E.EMP_NM
;
-- 조회 컬럼 : 직원번호/성명/자녀명수
-- 자녀수 2명 이상 : REL_TYPE_CD A=27이 자녀이므로 이거 2ROW 이상
SELECT E.EMP_NO 직원번호, E.EMP_NM 성명, COUNT(*)
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE F.REL_TYPE_CD = 'A27' -- 자녀(A27) 조건 적용
GROUP BY E.EMP_NO ,E.EMP_NM
-- 조회에 필요한 EMP_NO,EMP_NM을 조회하기 위해 GROUP BY 사용 필요.
-- COUNT가 단일행 그룹 함수이기에 단일행 그룹함수 COUNT와 다른 행도 조회하기 위해 사용함
HAVING COUNT(F.REL_TYPE_CD) > 2;
3-4) 현재 기준 조직의 부서별 직원이 몇 명인지 구하는 sql을 작성하세요. (조직코드, 조직명, 직원수)
힌트 group by org_cd, org_nm
-- 현재 기준 : ?
-- 부서별 직원 : GROUP BY ORG_NM
-- 부서별 직원 수 : COUNT(*)
SELECT
org_nm 조직분류,
COUNT(*) 직원수
FROM
emp_c e
JOIN org_c o USING ( org_cd )
GROUP BY
org_nm;
;
전체 쿼리
--3)20210321 기준으로 재직중인 임직원이며,사내부부인 임직원의 직원번호를 구하는 sql을 작성하세요.
--HINT : EMP_C와 FAM_REL_C를 조인해야 합니다.
-- 조회 : 직원번호
-- 재직중 : RETIRE 99/12/31 조건
-- 사내부부 : ? // 일단 EMP_NO = EMP_REL_NO 아님
-- 직원번호와 직원가족번호 =조건 해봤으나 X
SELECT *
FROM EMP_C E
JOIN FAM_REL_C FR ON(E.EMP_NO = FR.EMP_NO)
WHERE E.EMP_NO = FR.EMP_REL_NO -- 직원번호와 직원가족번호 =조건 해봤으나 X
;
SELECT *
FROM EMP_C E
JOIN FAM_REL_C FR ON(E.EMP_NO = FR.EMP_REL_NO)
WHERE E.EMP_NO = FR.EMP_REL_NO -- 직원번호와 직원가족번호 =조건 해봤으나 X
;
SELECT *
FROM EMP_C E
JOIN FAM_REL_C FR ON(E.EMP_NO = FR.EMP_REL_NO)
WHERE E.EMP_NO = FR.EMP_REL_NO
AND REL_TYPE_CD IN('A02', 'A18')
;
SELECT * FROM EMP_C;
SELECT * FROM FAM_C;
SELECT * FROM ORG_C;
SELECT * FROM FAM_REL_C;
SELECT *
FROM EMP_C
WHERE EMP_NO = 11501824;
SELECT *
FROM FAM_C
WHERE EMP_NO = 11501824;
SELECT *
FROM FAM_REL_C
WHERE EMP_NO = 11501824;
--3-2) 현재 날짜 기준 재직중인 "이씨 성＂을 가진 직원의 자녀명수가 몇 명인지 리스트를 만드세요.
--힌트: substr, count(*)를 사용
-- 재직중 : RETIRE 99/12/31 조건
-- 현재 날짜 기준
-- 이씨성 : LIKE '이%'
-- 자녀수
-- 조건 : 재직중+이씨성
SELECT *
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND E.EMP_NM LIKE '이%'
;
-- COUNT + LIKE
SELECT E.EMP_NM 직원명, COUNT(*)
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND E.EMP_NM LIKE '이%'
GROUP BY E.EMP_NM
;
-- COUNT(SUBSTR)+ LIKE
SELECT E.EMP_NM, COUNT(SUBSTR(E.EMP_NM,2,4)) AS "자녀명수"
FROM EMP_C E
JOIN FAM_C F USING(EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND E.EMP_NM LIKE '이%'
GROUP BY E.EMP_NM
;
-- COUNT + SUBSTR
SELECT E.EMP_NM, COUNT(*) AS "자녀명수"
FROM EMP_C E
JOIN FAM_C F USING(EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND SUBSTR(E.EMP_NM, 1,1) = '이'
GROUP BY E.EMP_NM
;
--3-3) 자녀 명수가 2명 이상인 직원의 리스트를 구하세요.(직원번호?/?성명?/?자녀명수?)
--힌트 having
-- 조회 컬럼 : 직원번호/성명/자녀명수
-- 자녀수 2명 이상 : REL_TYPE_CD A=27이 자녀이므로 이거 2ROW 이상
-- A27타입인 자녀수만 조회
SELECT COUNT(F.REL_TYPE_CD)
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE F.REL_TYPE_CD = 'A27'
;
SELECT E.EMP_NO 직원번호, E.EMP_NM 성명, COUNT(*)
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE F.REL_TYPE_CD = 'A27' -- 자녀(A27) 조건 적용
GROUP BY E.EMP_NO ,E.EMP_NM
-- 조회에 필요한 EMP_NO,EMP_NM을 조회하기 위해 GROUP BY 사용 필요.
-- COUNT가 단일행 그룹 함수이기에 단일행 그룹함수 COUNT와 다른 행도 조회하기 위해 사용함
HAVING COUNT(F.REL_TYPE_CD) > 2
-- WHERE절에서 자녀있는 직원만 골라낸 후, HAVING으로 2명이상 조건 적용
-- 이후 GROUP BY 직원번호와 성명을 묶어서 조회
;
SELECT * FROM EMP_C;
SELECT * FROM FAM_C;
SELECT * FROM ORG_C;
SELECT * FROM FAM_REL_C;
--3-4) 현재 기준 조직의 부서별 직원이 몇 명인지 구하는 sql을 작성하세요. (조직코드, 조직명, 직원수)
--힌트 group by org_cd, org_nm
-- 현재 기준 : ?
-- 부
-- 부서별 직원 수 : COUNT(*)서별 직원 : GROUP BY ?(ORG..?)
SELECT
org_nm 조직분류,
COUNT(*) 직원수
FROM
emp_c e
JOIN org_c o USING ( org_cd )
GROUP BY
org_nm;
;
--문제1) 직원번호 10004141 의 이름은? 힌트 : emp_c에 있음
-- 최ㅇ규
SELECT EMP_NM FROM EMP_C
WHERE EMP_NO = 10004141;
SELECT * FROM EMP_C
WHERE EMP_NO = 10004141;
--문제2) 직원번호 10004141 는 몇 명의 가족이 있나요? 힌트 fam_c
-- 5명
SELECT * FROM FAM_C
WHERE EMP_NO = 10004141;
--문제3) 직원번호 10004141 의 소속 부서 코드는? 힌트 emp_c
-- A183500
SELECT ORG_CD FROM EMP_C
WHERE EMP_NO = 10004141;
--문제4) 직원번호 10004141 의 소속 부서 명은? 힌트 org_c
-- 전XX룹
SELECT ORG_NM FROM ORG_C
WHERE ORG_CD = 'A183500';
--문제5) 남자 / 여자 직원이 몇 명인지 한번에 알 수 있는 sql을 작성하세요. (group by gender_cd)
-- 남 1374명 // 여 621명
-- 전체 개수를 세어야하니 COUNT집계 함수 사용해야함
--SELECT COUNT(GENDER_CD) --DECODE(SUBSTR(GENDER_CD,1,1),1,'남','여') AS 성별
--FROM EMP_C
--GROUP BY GENDER_CD;
--HAVING GENDER_CD = 2; -- 필요없는 조건
SELECT GENDER_CD,COUNT(*)
FROM EMP_C
GROUP BY GENDER_CD;
-- DECODE+ALIAS로 분류 컬럼 추가
SELECT DECODE(SUBSTR(GENDER_CD,1,1),1,'남','여') AS 성별, COUNT(*)
FROM EMP_C
GROUP BY GENDER_CD;
--문제6) 직원별 자녀가 몇 명인지 한번에 알 수 있는 sql을 작성하세요. (rel_type_cd가 A27이면 자녀)
--
SELECT *
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE REL_TYPE_CD = 'A27';
--WHERE EMP_NO = 10004141;
-- WHERE문에 E.EMP_NO = 10004141 추가로 넣어서 사원 한명의 자녀 목록 확인
SELECT E.EMP_NO, EMP_NM, E.BIRTH_YMD, FAM_NM, REL_TYPE_CD,F.BIRTH_YMD
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE REL_TYPE_CD = 'A27'
AND E.EMP_NO = 10004141; -- 10004141 최ㅇ규 19551223 최ㅇ철 A27 19850503
-- 자녀 둘인 걸 확인 했으니 COUNT()로 자녀수 조회ㄱㄱ
SELECT COUNT(E.EMP_NO)
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE REL_TYPE_CD = 'A27'
AND E.EMP_NO = 10004141; -- 2
-- 전체 직원의 자녀수 인듯. 직원 별 자녀수가 필요
SELECT COUNT(E.EMP_NO)
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE REL_TYPE_CD = 'A27'; -- 2412
-- 직원 별이니까 GROUP BY 통해 묶고 대상은 EMP_NO이면 될 듯
SELECT E.EMP_NO, COUNT(E.EMP_NO)
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE REL_TYPE_CD = 'A27'
GROUP BY E.EMP_NO; -- 인출행 수 1239
--SELECT E.EMP_NO,E.EMP_NM, COUNT(E.EMP_NO)
--FROM EMP_C E
-- JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
--WHERE REL_TYPE_CD = 'A27'
--GROUP BY E.EMP_NO; -- 인출행 수 1239 -> 조회할려는 컬럼이 그룹바이로 지정이 안됐기에 에러 발생
-- ANSI 표준 JOIN
SELECT E.EMP_NO, E.EMP_NM, COUNT(E.EMP_NO) "자녀수"
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE REL_TYPE_CD = 'A27'
GROUP BY E.EMP_NO, E.EMP_NM; -- 인출행 수 1239
-- 오라클 전용 JOIN구문
SELECT E.EMP_NO, E.EMP_NM, COUNT(E.EMP_NO) "자녀수"
FROM EMP_C E, FAM_C F
WHERE E.EMP_NO = F.EMP_NO
AND REL_TYPE_CD = 'A27'
GROUP BY E.EMP_NO, E.EMP_NM; -- 인출행 수 1239
ORA-00979: GROUP BY 표현식이 아닙니다. 00979. 00000 - "not a GROUP BY expression"
상황
아래 쿼리를 조회하려다 발생
SELECT E.EMP_NO, E.EMP_NM, COUNT(E.EMP_NO)
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE REL_TYPE_CD = 'A27'
GROUP BY E.EMP_NO; -- 인출행 수 1239
원인
조회할려는 컬럼이 그룹바이로 지정이 안됐기에 에러 발생
GROUP BY문에 들어가 있는 컬럼명들 중에 SELECT문에 안들어가도 있어도 에러발생X
반대로, GROUP BY문에 없는데 SELECT문에서 들어가있는 컬럼명이 있다면 에러발생O
해결
GROUP BY E.EMP_NO에 추가로 E.EMP_NM 컬럼 추가
E.EMP_NM이 조회할려던 컬럼
즉, 조회할려는 컬럼이 그룹바이로 지정이 안됐기에 에러 발생
SELECT E.EMP_NO, E.EMP_NM, COUNT(E.EMP_NO)
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE REL_TYPE_CD = 'A27'
GROUP BY E.EMP_NO, E.EMP_NM; -- 인출행 수 1239
