SELECT list expression references column amount which is neither grouped nor aggregated at [12:7]

SELECT list expression references column amount which is neither grouped nor aggregated at [12:7]

 

실행했던 코드

-- SELECT * 
-- FROM advanced.orders
SELECT
  SUM(amount),
  SUM(amount) OVER (ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY order_id) AS cumulative_sum_by_user,
  AVG(amount) OVER (ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_order_avg_amount
FROM advanced.orders
ORDER BY order_id

 

 

오류해결 코드

-- SELECT * 
-- FROM advanced.orders
SELECT
  -- SUM(amount),
  SUM(amount) OVER() AS amount_total, # OVER() 추가
  SUM(amount) OVER (ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY order_id) AS cumulative_sum_by_user,
  AVG(amount) OVER (ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_order_avg_amount
FROM advanced.orders
ORDER BY order_id

 

SUM() 집계함수 에서 PARTITION, ORDER BY 작성없이 OVER()  만 추가해도 실행될 수 있다.

오류가 발생했던 코드는 OVER 없이 집계함수로만 작성,

이후 컬럼들은 SELECT에서 OVER와 함께 윈도우 함수로 작성되어 오류가 발생했던 것 같다.

 

OVER()을 함께 작성해야 그룹화 없이도 다른 열과 함께 사용이 가능해지기 때문에,

GROUP BY를 사용하지 않고도 오류 없이 실행될 수 있었다.

 

 

실습 강의