[SQL] GROUP BY, ORDER BY 함께 사용 쿼리 주의 사항 (실습 스크립트 포함)
요약
group by, order by 함께 썼을 때 반드시 select절에 있는 컬럼이거나 group by 절에 있는 컬럼만 order by에 사용할 수 있습니다(SQL 문맥 파악 필수).
먼저 연습 데이터를 확인하고 가능한 쿼리 세 가지를 보고 마지막에 문제가 발생하는 쿼리를 확인하겠습니다. 가장 하단에 테스트 유저와 테이블스페이스, 데이터에 대한 생성, 권한 부여, 데이터 삽입 등의 실습을 위한 쿼리를 추가했습니다. 직접 여러가지 케이스를 만들어서 실습해보면 많이 도움 될겁니다.
연습환경은 오라클 19C에서 진행했지만 대부분 ANSI 표준이니 참고해주세요.
연습 데이터
SQL> select * from t1 order by 1,2;
C1 C2 C3
---------- ---------- ----------
1 10 20
1 20 10
2 10 30
2 20 20
2 30 10
3 10 10
1번
SELECT c1, sum(c2) as c2
FROM t1
GROUP BY c1
ORDER BY c2 desc;
C1 C2
---------- ----------
2 60
1 30
3 10
2번
ORDER BY에 사용된 C3 컬럼은 SELECT 절에 쓰지 않았지만 오라클은 행기반 데이터베이스이기 때문에 데이터를 액세스할 때 행 전체 컬럼을 메모리에 로드하는 특성때문에 SELECT 절에 기술되지 않은 컬럼으로도 정렬을 할 수 있습니다.
select c1, c2
from t1
order by c3 asc;
C1 C2
---------- ----------
3 10
1 20
2 30
2 20
1 10
2 10
3번
select c1, sum(c2) as c2
from t1
group by c1
having sum(c2) > 10
order by count(*) asc;
C1 C2
---------- ----------
1 30
2 60
[에러] 4번
ORDER BY 절에 기술된 컬럼 C3는 GROUP BY 절에 기술되지 않은 컬럼이기 때문에 행 기반 데이터베이스(오라클)이라 하더라도 에러가 발생합니다. 위 1번과 비교해보면 1번에서는 C2 컬럼을 SELECT 절에서 그룹 함수인 SUM 함수를 사용해주었고 그 C2 컬럼을 다시 ORDER BY 절에 사용했기 때문에 정상적으로 쿼리가 되는 것입니다. 그러나 지금 4번의 C3 컬럼은 GROUP 함수를 사용해 그룹핑을 해주지도 않았고 GROUP BY 절에도 등장하지 않기 때문에 ORDER BY에서 C3을 사용하려고 했을 때 오류가 나는 모습입니다.
select c1, sum(c2) as c2
from t1
group by c1
order by c3 desc;
order by c3 desc
*
ERROR at line 4:
ORA-00979: not a GROUP BY expression
추가
5. [성공] order by에 사용한 컬럼이 group by에 없음, select절에 있음
SQL> select c1, sum(c2) as c2
2 from t1
3 group by c1
4 order by c2 desc;
C1 C2
---------- ----------
2 60
1 30
3 10
6. [성공] order by에 사용한 컬럼이 group by에 있음, select절에 있음
SQL> select c1, sum(c2) as c2
2 from t1
3 group by c1
4 order by c1 desc;
C1 C2
---------- ----------
3 10
2 60
1 30
7. [에러] 문제의 3번 보기와 마찬가지로 c3로 group by를 해줬는데 c1에 대해선 그룹 함수 처리를 안했기 때문에 에러 발생
SQL> select c1, sum(c2) as c2
2 from t1
3 group by c3
4 order by c3 desc;
select c1, sum(c2) as c2
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
8. [성공] 이번에는 c1, c3 두 컬럼으로 group by를 했기 때문에 order by절에서 c3를 사용하고 select에선 c3를 조회하지 않아도 정상적으로 실행은 되었다. 그러나 결국 전체 건수를 다 출력하게 되어 의미상으로 무의미한 쿼리가 됨.
SQL> select c1, sum(c2) as c2
2 from t1
3 group by c1,c3
4 order by c3 desc;
C1 C2
---------- ----------
2 10
1 10
2 20
1 20
2 30
3 10
9. [성공] 위 5번과 마찬가지로 결국 전체 건수를 다 출력하고 c2로 내림차순 정렬이 되어서 나온다.
SQL> select c1, sum(c2) as c2
2 from t1
3 group by c1,c3
4 order by c2 desc;
C1 C2
---------- ----------
2 30
2 20
1 20
2 10
1 10
3 10
10. [성공] order by에 사용한 컬럼이 group by에 없음, select절에 있음. 특이사항 없음
SQL> select c1, sum(c2) as c2
2 from t1
3 group by c1
4 order by c2 desc;
C1 C2
---------- ----------
2 60
1 30
3 10
유저, 테이블스페이스, 테이블 생성, 권한 부여 및 데이터 삽입
SQL> set pages 5000
SQL> set line 300
SQL> select username from all_users;
SQL> grant create session to test01 identified by "test01";
SQL> grant create table to test01;
SQL> create tablespace tbs_test_01 datafile 'tbs_test_f1.dbf' size 1M autoextend on online;
SQL> alter user test01 default tablespace tbs_test_01;
SQL> alter user test01 quota unlimited on tbs_test_01;
SQL> conn test01/test01
SQL> create table test01.t1 (c1 varchar2(10) not null primary key, c2 varchar2(10) not null, c3 varchar2(10) not null) tablespace tbs_test_01;
SQL> alter table test01.t1 modify primary key disable cascade;
SQL>
insert into test01.t1 (c1, c2, c3)
values (1, 10, 20);
insert into test01.t1 (c1, c2, c3)
values (1, 20, 10);
insert into test01.t1 (c1, c2, c3)
values (2, 10, 30);
insert into test01.t1 (c1, c2, c3)
values (2, 20, 20);
insert into test01.t1 (c1, c2, c3)
values (2, 30, 10);
insert into test01.t1 (c1, c2, c3)
values (3, 10, 10);
SQL> select * from t1;
SQL> commit;