[내일배움캠프]_달리기반 Lv5

가장 많이 팔린 품목은?

1. 각 고객이 구매한 모든 제품의 총 금액을 계산하고, 고객 이름, 총 구매 금액, 주문 수를 출력하는 SQL 쿼리를 작성해주세요.

select a.CustomerName,
       b.TotalAmount,
       a.OrderCount
from 
(
      select C.CustomerName,
                 O.CustomerID,
                 count(O.Quantity) as OrderCount
      from customers c 
      join orders o on C.CustomerID = O.CustomerID 
      group by C.CustomerName, O.CustomerID
) a 
join (
      select O.CustomerID,
             sum(P.price*O.Quantity) as TotalAmount
      from products p 
      join orders o on P.ProductID = O.ProductID
      group by O.CustomerID
     ) b on a.CustomerID = b.CustomerID

 

서브커리 a

customers 와 orders를 join해  Quantity(수량)카운트 후 OrderCount로 지칭 

 

서브커리 b

products와 orders를 join해  p의 가격이랑 o의 수량을 곱한값을 더해 TotalAmount로 지칭

 

메인

a,b를 공통 테이블인 orders의 공통 컬럼CustomerID로 join해 출력

---

2. 각 제품 카테고리별로 가장 많이 팔린 제품의 이름과 총 판매량을 조회하는 SQL 쿼리를 작성해주세요.

select p.Category,
          p.ProductName as Top_Product,
          sum(o.Quantity) as TotalSold
from products p
join orders o on p.ProductID = o.ProductID
group by p.Category, p.ProductName
having sum(o.Quantity) = (
    select max(TotalSold)
    from (
           select  sum(o2.Quantity) as TotalSold
           from products p2
           join orders o2 on p2.ProductID = o2.ProductID
           where p2.Category = p.Category
           group by p2.ProductName
           ) a
)

 

- sum(o.Quantity) : 수량총합 group by로 카테고리별, 제품이름별로 정렬

[having절]

select절과 같게 sum 수량/제품이름으로 정렬 후 max로 최대값 반환

*having절은 group by보다 작동순서가 느려 having절에 카테고리로 그룹화 하지 않아도 group by에 의해 받아져 있는 상태

 

---

<스파르타 코드>

SELECT 
    c.CustomerName,
    SUM(p.Price * o.Quantity) AS TotalAmount,
    COUNT(o.OrderID) AS OrderCount
FROM 
    Customers c
JOIN 
    Orders o ON c.CustomerID = o.CustomerID
JOIN 
    Products p ON o.ProductID = p.ProductID
GROUP BY 
    c.CustomerName;

• JOIN을 사용해 고객, 주문, 제품 테이블을 결합한다.
• SUM(p.Price * o.Quantity)로 각 고객이 구매한 제품의 총 금액을 계산하고, COUNT(o.OrderID)로 주문 수를 계산한다.
• GROUP BY c.CustomerName으로 고객별로 결과를 그룹화한다
• 첫 JOIN에서 Orders의 약칭을 정해 2번째 JOIN에서 그냥사용
• 여러 테이블을 join할때는 이방식을 사용해 기존 (A JOIN B를 안하고 JOIN B로 엮는다)

 

SELECT 
    p.Category,
    p.ProductName AS Top_Product,
    SUM(o.Quantity) AS TotalSold
FROM 
    Products p
JOIN 
    Orders o ON p.ProductID = o.ProductID
GROUP BY 
    p.Category, p.ProductName
HAVING 
    SUM(o.Quantity) = (
        SELECT 
            MAX(SumQuantity)
        FROM 
            (SELECT 
                 p2.Category,
                 SUM(o2.Quantity) AS SumQuantity
             FROM 
                 Products p2
             JOIN 
                 Orders o2 ON p2.ProductID = o2.ProductID
             GROUP BY 
                 p2.Category, p2.ProductID

            )  AS Subquery
        WHERE 
            Subquery.Category = p.Category
    );

• JOIN을 사용해 제품과 주문 데이터를 결합하고, GROUP BY로 카테고리와 제품별로 그룹화한다.
• HAVING 절에서 서브쿼리를 사용해 각 카테고리에서 가장 많이 팔린 제품을 필터링한다.
• 질문해본 결과 WHERE은 서브커리 안으로 들어가는게 좋을것 같다고 말씀해주심
• 2번째 문제는 내가 작성한게 더 좋을것 같다

 

WHAT TO LEARN

1. 서브커리절의  where p2.Category = p.Category 같은 문장은 주커리와 동일시 시키고 해당 컬럼만 고려하게 하는것
2. 2개 이상의 테이블을 JOIN할 경우 기존 A JOIN B를 고수해 서브커리로 묶지말고 JOIN B로 간추려 진행