QCC 1회차 문제(JOIN, HAVING)

문제:
1. 문제:
당신의 회사는 2011년 10월 동안 자사 제품을 많이 주문한 고객들에게 특별 할인 쿠폰을 제공할 예정입니다. 이를 위해 2011년 10월 한 달 동안 구매한 회사 제품 수량이 총 70개 이상인 고객을 찾아주세요.

• 취소 된 주문은 제외해주세요.
• 고객 정보를 포함하여 고객 ID, 이름, 성, 총 주문 수량을 출력하세요.
• 결과는 고객 ID 기준으로 오름차순 정렬해야 합니다.

2. 테이블:

-sales_order_header: 각 주문의 기본 정보를 저장하는 테이블(컬럼:sales_order_id, customer_id, order_date, total_due, status)

-sales_order_detail: 각 주문의 세부 정보를 저장하는 테이블(컬럼:sales_order_id, sales_order_detail_id, product_id, order_qty, unit_price, unit_price_discount, line_total)

-sales_customer: 각 고객의 정보를 저장하는 테이블(컬럼: customer_id, person_id, store_id, territory_i)

-person: 각 고객의 인적 정보를 저장하는 테이블(컬럼: business_entity_id, person_type, name_style, title, first_name, middle_name, last_name, suffix, email_promotion, modified_date)

3.풀이:

SELECT C.customer_id,
       C.first_name,
       C.last_name,
       B.sum_qty
FROM 
  (
  SELECT A.customer_id,
       SUM(A.order_qty) >= 70 sum_qty
  FROM 
    (
    SELECT h.customer_id,
       d.order_qty
    FROM sales_order_header h INNER JOIN sales_order_detail d ON h.sales_order_id = d.sales_order_id
    WHERE h.order_date like ('2011-10%') AND h.statues <> 6
    ) AS A
  GROUP BY A.customer_id
  ) AS B
JOIN 
(
SELECT c.customer_id,
       p.first_name,
       p.last_name
FROM sales_customer c LEFT JOIN person p ON c.person_id=p.business_entity_id
) AS C
ON B.customer_id=C.customer_id
ORDER BY C.customer_id ;

❌문제점:

SELECT A.customer_id,
       SUM(A.order_qty) >= 70 sum_qty

• 이렇게 하면, SUM(A.order_qty) >= 70의 결과는 TRUE(1) 또는 FALSE(0).
  즉, 집계 함수(SUM())를 사용한 결과를 비교 연산(>= 70)으로 변환하면 논리 값(Boolean)이 출력됨.

• sum_qty 컬럼에는 1 또는 0만 들어가게 된다.
  그런데 필요한 건 총 주문 수량(SUM 값 자체)이지, Boolean 값이 아님.

⭕️해결방법:

• HAVING 절 사용
  원하는 특정 조건(SUM(order_qty) >= 70)을 만족하는 데이터만 가져오려면, HAVING SUM(A.order_qty) >= 70을 사용

• HAVING을 사용하는 이유
  WHERE는 개별 행(row) 필터링에 사용
  HAVING은 GROUP BY로 집계된 결과를 필터링하는 데 사용
  SUM() 같은 집계 함수는 GROUP BY가 적용된 후에 계산되기 때문에
  👉 HAVING을 써야 제대로 작동!

📌기억할 점

• SELECT에서 집계함수와 연산자를 사용하면 논리값이 나온다!

• SELECT에서 집계 함수(SUM, AVG, COUNT, MAX, MIN 등)에
  비교 연산자(>=, <=, =, != 등)를 사용하면
  논리 값(BOOLEAN, 즉 TRUE 또는 FALSE)이 반환

✅ 즉, SELECT SUM(...) >= 70 하면 → 1(TRUE) 또는 0(FALSE)가 출력됨!
✅ 반면, HAVING SUM(...) >= 70은 그룹핑된 결과에서 조건을 걸 때 사용함.

-수정ver-
SELECT C.customer_id,
       P.first_name,
       P.last_name,
       B.total_qty
FROM 
  (
  SELECT h.customer_id,
         SUM(d.order_qty) AS total_qty
  FROM sales_order_header h 
  INNER JOIN sales_order_detail d 
  ON h.sales_order_id = d.sales_order_id
  WHERE h.order_date BETWEEN '2011-10-01' AND '2011-10-31'
        AND h.status <> 6
  GROUP BY h.customer_id
  HAVING SUM(d.order_qty) >= 70
  ) AS B
JOIN sales_customer C ON B.customer_id = C.customer_id
JOIN person P ON C.person_id = P.business_entity_id
ORDER BY C.customer_id;

+지피티 추가 연습 문제(2개 이상의 테이블 JOIN)

/* 각 고객별 총 주문 금액을 구하시오. */
SELECT c.customer_id,
	   c.first_name,
	   c.last_name,
	   SUM(od.quantity * od.price) AS total_amount
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id 
GROUP BY c.customer_id, c.first_name, c.last_name;

/* 각 직원별 총 판매수량을 구하시오. */
SELECT e.employee_id,  -- 오타 수정
	   e.first_name,
	   e.last_name,
	   SUM(od.quantity) AS total_quantity
FROM employees e 
JOIN orders o ON e.employee_id = o.employee_id
JOIN order_details od ON o.order_id = od.order_id
GROUP BY e.employee_id, e.first_name, e.last_name  -- 오타 수정
HAVING SUM(od.quantity) >= 100;

/* 카테고리별 총 판매 금액을 구하시오. */
SELECT c.category_id,
	   c.category_name,
	   SUM(od.quantity * p.price) AS total_sales
FROM categories c 
JOIN products p ON c.category_id = p.category_id
JOIN order_details od ON p.product_id = od.product_id
GROUP BY c.category_id, c.category_name;

/* 가장 많이 팔린 상위 5개 제품을 구하시오. */
SELECT p.product_id,
	   p.product_name,
	   SUM(od.quantity) AS total_quantity
FROM products p 
JOIN order_details od ON p.product_id = od.product_id  -- 오타 수정
GROUP BY p.product_id, p.product_name
ORDER BY total_quantity DESC
LIMIT 5;