GROUP BY (下) : 오류문 해결하기

여러분이 숙련된 SQL 사용자이든 이제 갓 SQL을 배우기 시작한 초보자이든 상관없이 쿼리를 실행하다 보면 각종 오류와 마주하게 됩니다. 오류 메시지를 읽으면 그걸 읽고 해결할 수 있어야 하는데, 종종 오류 메시지가 도움이 안 될 때가 있습니다. 그중 하나가 바로 GROUP BY 오류 메시지입니다. 이번 글에서는 해당 오류에 관해 살펴보고, 이를 어떻게 해결할 수 있을지 알아보고자 합니다.

여러분이 GROUP BY 에 관한 기본적인 개념을 숙지하고 있다는 가정하에 설명하도록 하겠습니다. 만약 SQL에서 데이터를 그룹화하는 데 아직 익숙하지 않으시다면, 지난 글인 SQL GROUP BY (上) : 개념과 실제 사용 방법  을 읽어보시길 추천드립니다.

GROUP BY 에 관해 알고 계신다면, 아마 이 사실을 기억하고 계실 겁니다. SELECT 문에 있는 모든 열은 집계 함수가 되거나 GROUP BY 절에 나타나야 합니다. GROUP BY 절을 사용하는데 만약 SELECT 문에 집계 함수를 사용하지 않거나 GROUP BY 절에 언급되지 않은 열이 존재한다면 오류가 발생합니다. 오라클에서는 GROUP BY 의 오류 메시지를 "GROUP BY 표현식이 아닙니다(Not a GROUP BY expression)" 라고 표현하고, 빅쿼리에서는 "집계 함수나 GROUP BY 절에 없으므로 SELECT 목록에서 사용할 수 없습니다(SELECT list expression references column x which is neither grouped nor aggregated at...)" 라고 표현합니다. 해당 오류 메시지는 SQL 초보자에게는 꽤나 헷갈릴 수 있습니다.

왜 이런 오류가 발생하는지 예시를 통해 살펴보도록 하죠. 아래와 같이 고객 정보가 담긴 테이블이 있다고 가정해 봅시다. 해당 테이블에는 고객에게 부여된 고유 아이디, 이름, 사는 곳, 마지막 구매 날짜, 총구매액에 관한 정보가 저장되어 있습니다.

id	name	city	state	last_purchase_date	purchases
1	Peter White	San Francisco	CA	2020-09-09	120.85
2	Helen Brown	San Francisco	CA	2019-04-11	24.85
3	Jeff Grey	Los Angeles	CA	2020-03-23	1085.00
4	Kristine Black	New York	NY	2020-10-02	230.50
5	Donald Page	New York	NY	2020-06-30	2345.00
6	Robert Lee	Los Angeles	CA	2019-12-06	11.00
7	Patrick Collins	San Francisco	CA	2020-02-12	200.30
8	Kate Nord	Buffalo	NY	2020-05-25	435.00

우리는 특정 주에서 발생한 고객별 마지막 구매일과 평균 구매액을 알고 싶습니다. 아래와 같이 쿼리를 쓸 수 있을 것 같습니다.

SELECT
 state,
 city
 MAX(last_purchase_date) AS last_purchase.
 AVG(purchases) AS avg_purchases
FROM customers
GROUP BY state;

해당 쿼리를 실행하면, SQL은 아래와 같은 오류 메시지를 뱉어 냅니다.

Oracle : not a GROUP BY expression

PostgreSQL : column "customers.city" must appear in the GROUP BY clause or be used in an aggregate function

SQL Server : column "customers.city" is invalid in the select list because it is not contained in either an aggregated function or the GROUP BY clause

 

그렇다면 우리의 쿼리는 어떤 부분이 잘못된 것일까요?

 

왜 이런 오류 메시지가 발생하나요?

위 쿼리에서, 우리는 데이터베이스에게 총 4가지의 정보를 반환해달라고 요청했습니다. SELECT 문을 보면 첫 번째는 주에 관한 정보(state), 두 번째는 도시에 관한 정보(city), 세 번째는 마지막 구매일(last_purchase_date), 그리고 마지막으로 평균 구매액을 (avg_purchases) 적었습니다. 이 4개의 열 중에서 state 는 GROUP BY 절에 적혀 있고, 마지막 구매일이나 평균 구매액은 각각 집계 함수인 MAX 함수와 AVG 함수를 사용해 주었습니다. city 열 혼자만 덩그러니 놓여 있네요. 집계 함수가 사용된 것도 아니고 그렇다고 해서 GROUP BY 절에 적어준 것도 아닙니다.

해당 오류가 발생하는 이유는 바로 GROUP BY 가 하고 싶은 "데이터의 그룹화"를 하지 못하기 때문입니다. 즉, 현재 쿼리문이 그룹화를 할 수 없는 상태라는 이야기입니다. 위의 원본 데이터가 담긴 테이블로 돌아가 보면 캘리포니아 주(CA)에는 2개의 서로 다른 도시(샌프란시스코, 로스앤젤로스)에 관한 데이터가 있습니다. 뉴욕 주도 마찬가지로 2개의 서로 다른 도시(뉴욕, 버팔로)에 관한 데이터가 있습니다. 도시에 서로 다른 데이터가 있으므로 SQL은 어떤 값을 선택해서 보여줘야 할지 모릅니다. 캘리포니아 주의 마지막 구매일은 샌프란시스코에서 일어난 마지막 구매를 보여줘야 하는지, 아니면 로스앤젤로스에서 일어난 마지막 구매를 보여줘야 하는지 SQL 은 혼자 판단하지 못합니다. 그래서 GROUP BY 표현식이 아니라는 둥 집계 함수나 GROUP BY 절에 없으므로 SELECT 목록에서 사용할 수 없다는 오류 메시지를 보여줍니다.

state	city	last_purchase	avg_purchases
CA	San Francisco OR Los Angeles?	MAX(last_purchase_date)	AVG(purchases)
NY	New York OR Buffalo?	MAX(last_purchase_date)	AVG(purchases)

 

그럼 어떻게 해결하나요?

여러분이 어떤 결과를 얻고 싶은지에 따라 해결 방법은 다양합니다. 크게 3가지 정도 고려해 볼 수 있을 것 같네요.

 

첫 번째, GROUP BY 절에 추가하세요

GROUP BY 절에 city 열을 추가하시는 건 어떤가요? 만약 해당 열을 GROUP BY 절에 넣는다면 위의 고객 데이터는 주별, 도시별로 데이터가 그룹화되어서 산출될 겁니다. 쿼리문은 아래와 같이 쓸 수 있습니다.

SELECT
 state,
 city,
 MAX(last_purchase_date) AS last_purchase,
 AVG(purchase) AS avg_purchases
FROM customers
GROUP BY state, city;

주와 도시별로 데이터를 그룹화한 결과는 다음과 같습니다.

state	city	last_purchase	avg_purchase
NY	Buffalo	2020-05-25	435.00
CA	San Francisco	2020-09-09	115.33
CA	Los Angeles	2020-03-23	548.00
NY	New York	2020-10-02	1287.75

 

두 번째, SELECT 문에서 지워주세요

SELECT 문에서 city 를 과감히 없애는 것도 방법입니다. 주별로만 고객 데이터를 묶고 싶은 것이라면, 굳이 city 가 SELECT 문에 들어갈 필요는 없겠죠. 그럼 SELECT 문에서 city 를 지우고 쿼리문을 작성해보도록 하겠습니다.

SELECT
 state,
 MAX(last_purchase_date) AS last_purchase,
 AVG(purhchases) AS avg_purchases
FROM customers
GROUP BY state;

결과는 다음과 같습니다.

state	last_purchase	avg_purchases
CA	2020-09-09	288.40
NY	2020-10-02	1003.50

 

세 번째, 집계 함수를 사용하세요

city 열에 집계 함수를 사용해 보도록 하겠습니다. 오류문에서도 GROUP BY 절 아님 집계 함수를 언급하니까요. 다양한 집계 함수를 사용할 수 있을 것입니다. COUNT 함수도 있고, SUM 함수, AVG 함수, MAX 함수, MIN 함수 등이 있으니 원하시는 걸 사용해 보셔도 됩니다.

우리는 COUNT 함수를 사용해 각 주에 몇 개의 도시가 있는지 살펴보고자 합니다. 쿼리문은 아래와 같이 써 볼 수 있겠네요.

SELECT
 state,
 COUNT(DISTINCT city) AS cities_with_customers,
 MAX(last_purchase_date) AS last_purchase,
 AVG(purchases) AS avg_purchases
FROM customers
GROUP BY state;

결과는 다음과 같습니다.

state	cities_with_customers	last_purchase	avg_purchases
CA	2	2020-09-09	288.40
NY	2	2020-10-02	1003.50

 

이외에도 어쩌면 GROUP BY 절을 사용하지 않고 데이터를 그룹화할 수 있는 윈도우 함수를 사용하는 것 또한 방법입니다. 특정 열 기준으로만 데이터를 그룹화할 상황이라면 윈도우 함수를 사용하는 것도 추천드립니다. 만약 지금까지 설명드린 방법이 해결되지 않았다면, 1) 괄호들을 잘 닫아주었는지 2) 열 이름을 잘 적어주었는지 더블 체크해주시기 바랍니다.

원문 : How to Fix a 'Not a GROUP BY Expression' Error